Rolling 12 months excluding special accounting periods

[Former Member]

Experts,

I need to do a rolling 12 months report (month that user selects and previous 11 months) based on 0FISCPER. I already know how to use offsets on variables. My situation is unique in that when I do the offsets on a user entry variable, it also displays the special accounting periods of 0, 13, 14, 15, and 16. I want to exclude the special accounting periods in my offsets and show only the calendar periods and not the special accounting periods.

Is there a way to do this in BEx? If not, is there a way to exclude the special accounting period values in a customer exit?

Thanks,

K-D

Edited by: K-D Vo on Sep 9, 2008 6:46 PM

[Former Member · Accepted solution]

Hi KD,

As you need to calculate rolling 12m based on calendar month rather than on fiscal period. I would suggest you to have a calendar month in the cube. then restrict the query with user input varaible for calendar month -11 to calculate rolling 12 m

Hope this helps.

PV.

[Former Member · Accepted solution]

The problem with the solution of the calendar month is that the special periods input will be assigned to a calendar month, and won't be excluded from the query, as required.

[Former Member · Accepted solution]

Hi,

Sorry for not the dates used to populate the special fiscal period.

Try to create a user exit variable to process the logic to exclude the special period and restrict the keyfigure with that variable.

if in period 6 then

Period 1- 6 of current year

Period 7 - 12 of previous year

If period 9

Period 1 -9 of current year

Period 10- 12 in previous year

Include this logic in user exit and use the variable for restriction in the query.

Hope this helps

PV

[Former Member · Accepted solution]

Thanks for all your responses. They have been helpful, but I don't think they will work for what I need. The restricted key figure options combines all the data into one value. I need the values separated by each month and showing the values per month.

The calendar month option I don't think will work either as you will have to map calendar month from posting period in the transformation rules and I'm not sure how the system will respond when it tries to map period 0, 13, 14, and 16 to a calendar month. It might error out or it might map it just like it maps to fiscal period and then you have the same problem.

I think a customer exit is the way to go, but I'm having trouble in how to exclude the periods in the logic.

the l_s_range-low and l_s_range-high values in the ABAP code is expecting a single value and not a range of values. If you try to pass it a range of values, it will not return the proper values. Anybody have ideas how to work around this problem?

[Former Member · Accepted solution]

Simple - use a select option variable then fill it with multiple single values (ie 12 lines)

[Former Member · Accepted solution]

Use the restricted KF I proposed, and drill across on the fiscal period/calendar month, it should display correctly the data split by period.

[Former Member · Accepted solution]

Simon - your suggestion would get the desired end result, but not in the manner the client wants it in. They don't want to have to choose the 12 months. They want to choose just one month (usually the current month) and have the report dynamically go back 11 previous months for them. If I can't find another solution, then I may have to try and convince them to choose the 12 months manually themselves.

Frederick - I'm still unclear about one part of your RKF solution. For the second hidden RKF where you exclude the special accounting periods, how do you know what offset to exclude if you don't know what period the end user will chooose? One user could choose a different month than another user. Do you exclude the actual hard-coded value of 000/2008 for example? If that's the case, then you would have to exclude all the special accounting periods for all the years available in the cube and you would have to continue to maintain it as new years of data are added to the cube. Is that how you're doing it?

Thanks,

K-D

[Former Member · Accepted solution]

I actually do what you require using my method - you put in one month - this is always in column 12

Column 11 has a cmod variable reading the month selection and works out whetehr it is -1 (or in the case of a year end 12 and not 13-16)

Column 10 has a cmod variable reading the month selection and works out whether it is -2 (or in the case of a year end 11 and not 13-16)

etc..

[Former Member · Accepted solution]

Check if you have the 0fiscper3 object in your cube, it's the period without the year.

If not, you'll have to create a customer exit variable which determines the list of the periods to be excluded.

[Former Member · Accepted solution]

I do have 0FISCPER3 in my cube, but doing an offset on that won't work across years. 0FISCPER3 has finite values (0-16) whereas 0FISCPER has an infinite number of values. If you try to do an offset of -11 of 0FISCPER3 and the user chooses period 4, it will either error out or not give you the right value. It also doesn't change the year to the prior year.

Edited by: K-D Vo on Sep 18, 2008 1:34 AM

[Former Member]

Create 2 KF, first one containing the rolling 12 months, second containing the to-be-excluded data, hide both, and create a calculated KF which will be RKF1 - RKF2.

Just check that in the rolling 12 months, the to be excluded periods are really in, or you will have a wrong result.

For the offset, it's of course not a -11 to be done, if using fiscper, but a -12 or 13 ... depending on the number of special periods in your fiscal calendar.

[Former Member]

I just created 12 RKFs with 11 cmod variables working of the input fiscal year/period