cancel
Showing results for 
Search instead for 
Did you mean: 

Rolling Forecast - Fiscal Year/Period Issue

Former Member
0 Kudos

Hi Everyone,

I'm new to Integrated Planning and reaching out for help regarding the issues I have while developing a 12 month rolling forecast for business users.

This is the first time we are trying to implement some thing using IP. Currently we are on BW 7.31 SP11.

I read through all the documents on this topic but still having issues and that is why I'm starting another discussion.

Below are the details of requirement.

Create a 12 month rolling forecast for the current year.

Hierarchy in Rows

Fiscal year/period (1-12 Months) in Columns

Sales for Period in columns  ( input ready)

All months prior to current month are Actuals ( should be locked)

Starting from Current Month till last month are Forecasts/Plan data ( input ready)

What I have done so far:

I created a Real time infocube ( with all the required chars and key figures above).Currently this does not have any data ,it should be populated through user input query.

I created a multiprovider on top of Real time Cube for forecast data and Actuals Cube for actuals

I created an aggregation Level on top of Multiprovider

I created the filters ( to restrict on company code, hierarchy nodes and fiscal year variant)

I created a Bex Query and added

Hierarchy under columns

Fiscal year/Period , Actuals_Sales (RKF),Plan_Sales (RKF) under rows.

and the filters defined on the agg. level under characteristics restrictions.

Actual_sales (Restricted key Figure):

Sales Per Period

Restricted on 0INFOPROV to 'ACTUALS infocube'

Restricted on fiscal year/period to < (less than ) 'Current fiscal year period'

Restricted on Value Type to 'Actual'

Plan_Sales (Restricted Key Figure): Input ready

Sales Per Period

Restricted on 0INFOPROV to 'REAL TIME infocube'

Restricted on Fiscal Year/Period to >=(greater than or equal to) 'Current Fiscal Year/Period'

Char Restrictions:

Apart from the agg level filters (global) ,I have added the Fiscal Year/Period and restricted it to 1-12 months of current year ( I don't have an existing variable/exit to use so I think I need to create one but for testing I'm restricting it to 1-12 months of the current year.

For Fiscal Year/Period under columns :

I set it to use the Master Data Values.

When I run the query, I see actuals till April ( since its QA, there is no data for May),but all the forecast and actuals columns are repeated for all 12 months.

Like:

                                         01/2015             01/2015          02/2015               02/2015          03/2015               03/2015

                                        Actuals_Sales    Plan_Sales     Actuals_sales      Plan_sales    Actuals_Sales      Plan_sales

Hierarchy

Operating Costs

Maintenance Costs

Salaries

Can you please advice if I'm missing anything here?

The requirement is to display the actuals(locked for editing) and forecast months should be input ready to enter the sales values and save it to Real Time Infocube through the query.

Accepted Solutions (0)

Answers (1)

Answers (1)

cornelia_lezoch
Active Contributor
0 Kudos

Hello reddy,

it is the simple question we have to ask for every plan query - what is the "driver" for the table we want to see.

In a query you can either see

booked values

char values according to char relationships or

char values according to master data.

so in your case I assume, that there is no booked data yet for forecast, so the booked value will not work.

if you define a structure with one column for actuals and one for forecast, you will end up with two columns per month.

so you need to bring the data together, so that it is shown in only one column.

Either you copy actuals to forecast. or you work with variables which identify dependent on a month selection, which column need to be filled with actaual and which needs to be filled with forecast.

the months that shall not be input ready then need to be protected by a data slice.

regards

Cornelia

Former Member
0 Kudos

Thank you Cornelia,

I was able to create the formula to display the months under one column.

Created a formula called Month and included the IF to display Plan_Sales during forecast months else Actuals_Sales. I set the 2 keyfigures (Plan_sales and Actuals_Sales) to hide.

But though I have the planning query set to change mode and Plan_Sales is set to input ready, In Analysis when I check it is not showing as input ready?

Is there anything that I need to set?

Thank you for your help.

cornelia_lezoch
Active Contributor
0 Kudos

Hi,

if you want to work with hidden key figures - which is not what I suggested - then you need to set the plan key figure to "data entry enabled", define the formula, use a reverse formula and set this also to data entry enabled. go to SAP help to read about usage of reverse formulas.

but this seems to me quite complicated.

what I meant is creating a restricted key figure,

make it data entry enabled

define Variable - probably for cube and value type (and maybe more char depending what is different between actuals and forecast)

depending on the month the variables either select what defines actuals or what defines forecast.

But you will need 12 sets of those variables.

If I think about it, maybe the reverse formula is not such a bad idea.

regards

Cornelia

Former Member
0 Kudos

Hi,

So I need to have

1 RKF - Plan_Sales (which is restricted on cube, value type, version)

1RKF - Actuals_Sales ( restricted on Actual cube ,value type, version)

12 variables ( 1 for each month) to display the above data.

But where do I specify the date restriction depending on which the actuals or plan data is displayed?

Can you please explain in detail?

Thank you.

Former Member
0 Kudos

HI Gurus,

Can anyone help me on this.

I created the inverse formula also ( there is only one key figure for input).So basically there is no reverse formula here.

But even then it doesn't work. It is still not showing the planning related options enabled in Analysis for input.

I would like NOT to go in the Inverse formula path.

Can please let me know what am I missing or what's the best approach to create this classic 12 month rolling forecast. ( Rows - Account Hierarchy, Columns - Posting Period/Fiscal year Period, Sales. Sale should be input ready so they can enter he values through Query from Analysis).

Thank you.

former_member199573
Active Participant
0 Kudos

Hi,

As replied above by Cornelia, you have to use 12 variables that are processed in customer exit to determine the value type that should be shown based on the current period.

---------------------------------

define Variable - probably for cube and value type (and maybe more char depending what is different between actuals and forecast)

depending on the month the variables either select what defines actuals or what defines forecast.

---------------------------------

You mentioned your understanding as below,

-------------------------

So I need to have

1 RKF - Plan_Sales (which is restricted on cube, value type, version)

1RKF - Actuals_Sales ( restricted on Actual cube ,value type, version)

12 variables ( 1 for each month) to display the above data.

-------------------------

can you create a structure in columns?

There should be one RKF for each of the 12 periods. So, one variable per RKF on characteristic Value type. The variable is processed in customer exit to get the value type that should be used to get the data from the multiprovider.

Regards,

Raja.

Former Member
0 Kudos

Hi,

Thank you for the details but can you please explain in detail with an example as I'm still not clear on whether the 2 different RKF's are required for Actuals and Plan or just one.

Also on creating the 12 RKF's using the Fiscal Year/Period.

Do I need to do any coding to accomplish this?

I have the Posting Period ,Fiscal Year and Fiscal Year/Period in the cube.

When the user enters the fiscal year/period as 007/2015 then Sales actuals should be displayed for 001/2015 to 006/2015 and 007/2015 onwards till 012/2015 should be sales plan allowing to input the data.

I really appreciate your help on this as I need to accomplish this task before the system is refreshed next week end.

Thank you.

Former Member
0 Kudos

Hi All,

Can anyone please advice if this is the approach as per the above discussions.

Create 12 exit variables for each month on Value Type or Version and below is the code for January

As I'm not an abap person so can you please verify and let me know if this code looks ok for what I'm trying to achieve.

DATA: l_s_var_range LIKE RRRANGEEXIT

  WHEN 'zvar_Jan_vtype'.

     IF I_step = 2.

          CLEAR l_s_range..

          l_s_range-SIGN ='I.'

          l_s_range-OPT='EQ'.

          LOOP AT I_t_var_range INTO l_s_var_range.

           WHERE vnam='fiscal year period user  input value'. 

                 IF l_s_var_range-low >= '001'.

                    l_s_range-low='010'. --- actuals

                  ELSE

                    l_s_range-low='020'. ---- plan

                  ENDIF.

           END LOOP.

     APPEND l_s_range TO e_t_range.

Please advice.

Thank you.

cornelia_lezoch
Active Contributor
0 Kudos

Hi preddy,

yes, looks good.

But if your actuals are in a different cube (what they usually are) you will need another variable for the cube selection with a similar code as above.

regards

Cornelia

former_member199573
Active Participant
0 Kudos

Hi,

If the version is segregating the data of two cubes(only one for plan and one for actuals) of the multiprovider, then the above logic would be sufficient. If there is more than one cube, then you need to determine it as mentioned by Cornelia.

Example, version 10 is only in one actuals cube and version 20 is only in one plan cube, So in the RKF, both the cubes can be included and the version would get the required data.

Please correct me if i am wrong.

Regards,

Raja.

cornelia_lezoch
Active Contributor
0 Kudos

if you have two cubes in one RKF it will not be data entry enabled

former_member199573
Active Participant
0 Kudos

sorry, my mistake. I was thinking it from only reporting perspective.

you are right.

So, pradeep, as mentioned by Cornelia, you should have another customer exit variable on 0INFOPROVIDER infoobject for each RKF and determine the cube name in the exit.

Regards,

Raja.

Former Member
0 Kudos

Hi All,

Thank you very much for the quick responses.

yes, I have 2 cubes ,one for ACTUALS and one for PLANNING.

SO just to reconfirm,

Need to create 12 RKFs for each month

Need to create 12 CE variables for each month on 0VERSION or 0VALUE TYPE

Need to create 12 CE variables for each month on 0INFOPROVIDER.

Restrict the monthly RKF's with above variables for each month.

Add the RKFs to columns

Add the Hierarchy to Rows

I have question on the 0Version and 0ValueType.

Do I need to use both for  planning query to work? Or can I just use the 0VERSION = '0' (Standard) for both actuals and plan but  then 0ValueType should be different like 'Actual' or 'Plan'?

Please advice.

Former Member
0 Kudos

Hi All,

Can anyone please confirm if my understanding and approach is right for the planning query to work so users can enter the sales data through that query.

Thank you.

former_member199573
Active Participant
0 Kudos

Hi,

For your question on version and value type,

0value type, with values 10 or 20 is used to differentiate the actual data and plant data.

0version can be used to differentiate the different planning versions. Ex.P01,P02,B01,B02,FC1_11,FC2_10 etc...

Raja.

Former Member
0 Kudos

Hi Raja,

Thank you for the clarification.

Sure, I understand that but for planning query if I just create the CE variables on 0ValueType then when while writing back to real time cube and if the user needs to save lets say 2 versions of plan data then do I need the separate Customer exit variables for each month even for the 0VERSION too?

I really appreciate if you can clarify on what all CE variables and RKF's are required for this planning to work.

Based on the above approach of creating RKF's and CE variables what should be set as input ready/data entry enabled within the query?

Thank you.

former_member199573
Active Participant
0 Kudos

Hi,

For your question about version of the plan data, the answer is yes, you would need it to be filled in another customer exit variable(ex ZVAR_VER1). 

Based on your requirement, it could be that the required planning version could be selected by the user in the selection screen in another variable(ZVAR_VER_INP) and you can use the value of this variable to fill the customer exit variable, ZVAR_VER1.

For your question about all the CE variables,

you need 12 CE variables for each month on 0VERSION and if you are not using 0VALUE TYPE, you would not need another set of variables, otherwise you would need them.

you need 12 CE variables for each month on 0INFOPROVIDER.

Raja.

Former Member
0 Kudos

Hi Raja,

Thank you.

I understand now but why do I need to create 12 variables for each month? Instead can I just have one CE variable and compare the fiscal year period (customer input value) against the system date/month and if it is <system month then display the actuals else plan values?

Similarly one CE variable for Value Type and if needed one for Version also.

Also,I have a question on the Hierarchy that I'm using in the rows.

I have restricted the hierarchy to few nodes without any child nodes/leaves in the planning filters.

In the query ,I have it under rows so will it allow to input the amount at NODE level and save that to real time cube ?because the users doesn't want to input  the value at child nodes level.

Please advice.

Former Member
0 Kudos

Hi Team,

I really need some help from you all who has experience working with input ready queries.

Before I have all the above customer exist variables and RKF's,

I wanted to see if at least the query when executed shows input ready cells and static cells.

SO here is what I did.

I'm thinking this could be related to Hierarchy or some other but please advice.

I have the

Account By Functional  Hierarchy in rows

Plan sales(input ready) , Actuals sales and Fiscal year period in columns

I have the filters created on agg level to include only few nodes of  the hierarchy.

I Would like to do the planning at Node level. ( is it possible?)

I have restricted all other characteristics ( nothing in the free characteristics).

All the required input ready query settings are enabled.

But when I run the query in Analysis, it doesn't show me the cells as input ready

Also the Planning related options in Analysis (like Save Data , Recalculate, Lock Cells) are disabled.

Please review and share your experiences as this critical to business and they want me to demo in next 1 week.

Thank you.

former_member199573
Active Participant
0 Kudos

Hi,

If you followed the discussion about each characteristic being unique for the cell to be input ready,

----------------From Cornelia,

if you have two cubes in one RKF it will not be data entry enabled.

----------------

So, check the value for your characteristics 0version, 0infoprovider.

Raja.

cornelia_lezoch
Active Contributor
0 Kudos

Hello,

there are many threads here about how to get a query data entry enabled.

Make sure to

  • have the cube in planning mode
  • define key figures and query as planning enabled
  • define each cell with single selection for each char used in the aggregation level. Don´t forget that unit, currency and info provider are also characteristics

Use single fixed selections for the first check and substitute those by variables only later, when you are sure that the query is defined correctly.

regards

Cornelia

Former Member
0 Kudos

Hi ,

Thank you for the input.

Yes, I read through all the documents that talk about the enabling the input ready required settings.

Also I DO NOT have the two cubes in one RKF,still the query is not input ready.

2 Actuals Cubes

One Planning cube which is in planning mode.

Multiprovider on top of this.

Agg Level is on MP.

Columns:

RKF_Actual Sales (restricted to 0valuetype= actual, 0version=0 ,2 actuals_cubes and fiscal year period <007.2015) - Input ready

RKF_Plan Sales (restricted to 0Valuetype =plan, 0version=0, plan_cube and fiscal year period >=007.2015)

Fiscal year Period

Rows:

Account by Functional Hierarchy ( filtered for few nodes).

All other characteristics( unit, currency type, functional area, posting period, fiscal year, fiscal year variant, company code) are restricted.

There is NO FREE characteristic.

But still the query doesn't allow to input. And it does show mw that the dev. is write back system and opens the query in change mode but the planning options are disabled in Analysis?

DO I NEED to filter the hierarchy to only show the leafs?

Currently BW hierarchy is setup to post the values at leaf level(account) and they get rolled up to node level.

SO I atleast want to test if I can input the values at leaf level?

Please advice.

Thank you.

cornelia_lezoch
Active Contributor
0 Kudos

it would be good to post a screenshot of your query definition.

0fiscper3 hast to be in column drilldown if you use it in the column restriction with >=

then take 0fiscper3 and 0fiscyear out of the aggregation level because you can not select those to single values and have several 0fiscper in your key figures.

Former Member
0 Kudos

Hi Cornelia,

I have the Fiscalyearperiod (0FISCPER) is restricted and is also under columns.

I have the Posting Period (0FISCPER3) and Fiscal Year are restricted to 1-12 periods and 2015.

Please see the attached screen shots of the query definition and Plan Sales - RKF

Please review and advice.

Thank you

Former Member
0 Kudos

Hi Cornelia,

The input query is finally allowing to input the values and saved back to the cube.

The culprit was the posting period and once I removed it from the agg. level then it now has the unique cell to input.

But I still need to exclude the period 0 and period00 from the fiscal year period as we only want 001-012.

Thank you for helping me get through the first step.


Raja and Cornelia and all others who ever replied, I greatly appreciate.

I will work on the CE  Variables and code now.

Thank you.

Former Member
0 Kudos

Hi,

If we cannot restrict a characteristic on multiple values then that is an issue for this query to be accurate.

Because, as part of the Hierarchy the compounded characteristic is automatically added to the agg.level and so If I restrict it to only one value called 'marketing' then output will not be accurate.

It should be restricted to Marketing and Manufacture but If I do that then the query is not input enabled any more and doesn't allow to input the values.

SO how to avoid adding this compounded char to Agg level or how can we restrict a char on multiple values in an input ready query?

Thank you.

Former Member
0 Kudos

Hi,

I would appreciate if anyone can verify the below abap code, because it doesn't give me the right output.

I have created 12 CE variables for Version ,12 CE for Inforprovider and 12 CE variables for ValueType and have below sample code for month1 (January) but it doesn't look like giving the right output.

Basically,the user will input the fiscal year period and to determine if that period is >=  period 001 on the columns to be able to display actuals /forecasts.. similarly for all other months...

Please advice.