Actual V/s Plan Data comparision in Query

[Former Member]

Hi SDNer

I am having a query based on MP which is used for Actual and plan data comparison. Plan data is coming from one cube and actual are coming from another cube.

Planning: Every month planning is done for coming 1 year. In jan 2007 we have plan data for Feb 2007 - Jan 2008. Planing month are denoted by char VERMONTH ( Version month) and cal month is simple 0CALMONTH.

Now the requirement is that in query user want to compare actual V/s Plan data. user will be inputing range for a variable created on 0CALMONTH. Now for every month we need to display actual data ( No issue directly available in cube month wise) and plan data for that month which was foretasted 3 months back

Ex: User input is 2007.04 - 2007.07

O/P

CALMONTH Actual Plan

200704 Actual of Apr Planing data of Apr which was done in Jan 2007

200705 Actual of May Planing data of May which was done in Feb2007

200706 Actual of June Planing data of June which was done in Mar 2007

200707 Actual of July Planing data of July which was done in June 2007

We can't go for cell referencing as client is not agree on that.

Looking for your help.

Thanks

Tripple k

Message was edited by:

Tripple k

[Former Member · Accepted solution]

Hi

You have the option of virtual keyfigures. This may solve the problem but you should live with the limitations of virtual keyfigures as you need to use them in all the queries in hidden mode built on the MP

Regards

N Ganesh

[Former Member · Accepted solution]

Hi SDBI

I was also thinking that as last option, anyway if somebdy has any other approach it would be appreciated. Cell referencing and back-end change are not to be considered.

Thanks

Tripple k

[Former Member · Accepted solution]

Hi

Then fill a new KF in the transformations itself catering to your requirement and take it to the MP. This new KF might have actual -3 plan months preceding values stored

If NO to any kinda coding, create another cube or ODS/DSO to include in your MP which will have the primary keys of the cubes and this KF ( Actual -3plan preceding months)

or variable exit

Regards

N Ganesh

[Former Member · Accepted solution]

might a customer exit variable on the plan kf help? The exit will read the input values of variable 0calmonth and derive the vermonth value from there?