Formula Variable

[Former Member]

In my scenario the user should be able to select the year and period.

I have created the following:

<b>For YTD Actuals</b>: Restricted on actuals KF and Posting Period (Value range Less than - 0P_PER3).

<b>For Forecast</b>: Restricted on forecast KF and Posting Period (Value range Greater than or equal to - 0P_PER3).

<b>For Months</b>:I have created 36 columns. 12 are RKFs for forecast, 12 are RKFs for actuals and 12 are CKFs.

I have hard coded each of the month columns. i.e., Posting period 1=jan, 2=feb etc.

<b>Till here the query works well</b>. But now I want to use the CKF which should be a total of Forecast & Actual.

I have created a formula variable with replacement path based on char Posting Period. This is filled with a variable (0P_PER3 - Posting Period (Single Entry, Mandatory).

Then I defined a boolean logic:

(1<Posting Period)Jan Actual + (1>=Posting Period)Jan Forecast - for January

(2<Posting Period)Feb Actual + (2>=Posting Period)Feb Forecast - for February

Etc till December.

<b>But this is not working</b>.

It is giving me only the Jan Forecast figures for all the months.

[Former Member]

did you try this that i posted for your same question.

PL try the following:

1. Create a rest key figure for YTD actual.

2. Create another rest key figure for forecast with the selections you have.

3. For columns 3-14, you do this.

Drag the variable fiscal year /period (range mandatory) and here you select fisc period from 003/2007 to 012/2007. These are your columns 3 to 14.

Ravi Thothadri

[Former Member]

Hi Ravi,

I think I have not made my requirement clear. Here is the requirement.

1. The user will make only one selection for year and one for period. Or he can make one selection for fiscal year/period. I cannot use ranges.

2. The year is not fixed to 2007. The user should be able to select the year that he wants the data for.

3.The columns will always be for all 12 months Jan-Dec.

4. Only the months prior to the selected month should display the Actuals figures and the selected month and the months after should display the forecast figures.

I really appreciate your help.

Thanks

NEWBW

[Former Member]

The user will make only one selection for year and one for period. Or he can make one selection for fiscal year/period. I cannot use ranges.

<b>You would need to use fiscalyear/period</b>

The year is not fixed to 2007. The user should be able to select the year that he wants the data for.

<b>That would be taken care by the variable on fiscalyear/period</b>

The columns will always be for all 12 months Jan-Dec.

<b>This could be a bit tricky, you need to create a customer exit variable on fiscal year and the input for the variable would be the fiscal year/period that has been passed by the user, to derive the year out of the variable and pass the same on year. Create 12 columns with selections of fiscalyear by customer exit, Restrict period by 1 – 12 (no matter what the user enters), and your KF</b>

Only the months prior to the selected month should display the Actuals figures and the selected month and the months after should display the forecast figures.

<b>Piece of cake, do an offset on the months prior the user wants to see ,if its only a month do a –1 on fiscalyear/period variable, and ur KF. This requirement is the one that would drive you to use the fiscalyear/period as a variable and not fiscal year and period for the user, the reason being if the user enters a period as 1, the system wouldn’t be able to interpret period 12 of the previous year( so you wouldn’t be able to report across the years)</b>