Macro calculation for DP (Need Simple macro)

[Former Member]

Hi,

There are 3 keyfigures.KF1,KF2 and KF3 in DP which is in weekly bucket.

Values

KF1

05/08/2009200 ;5/15/200930 ;05/22/2009400; 05/29/200950

KF2

05/08/200920 ;5/15/200930 ;05/22/200940; 05/29/200950

For KF3 we need to put the result for the month inthe cell at the end of the month (05/29/2009)

and this should be like this.

KF3=Sum of the KF1 (for the current fiscal month)+KF2(current fiscal week)

Now this is week 5/15/2009 and result in 05/29/2009 KF3 should be

Result in the KF3 should be sum of =680+30 that is equal to 710

(i.e.680=2003040050 Sum of the KF1 (for the current fiscal month)30 KF2(current fiscal week)

and assume I move to next week(05/22/2009) then result in 05/29/2009 KF3 should be

Result in the KF3 should be sum of =680+40 that is equal to 720

I wrote a macro for each cell which is very long .Is there any simle steps for this.

Thanks and Regards

Raghavendra Pai

[Former Member · Accepted solution]

Hello,

This solution might be of use only if you are using fiscal year variant in your storage bucket profile. You can write a macro as follows:

Step1. Define and initialise 3 layout variables; say VAR_KF1; VAR_KF2 and VAR_KF3.

Step2: Check month change based on fiscal year variant; if yes, then VAR_KF1 = sum of KF1 values in each week of that month.

Step3: Check current week based on fiscal year variant; if yes, then VAR_KF2 = KF2 value for that week.

Step4: Check last week of Fiscal Month based on fiscal year variant; if yes, VAR_KF3 = VAR_KF1 + VAR_KF2.

Assign VAR_KF3 to KF3 in the same condition loop.

Please let me know if this is of some use.

Warm regds,

Ashutosh

[Former Member · Accepted solution]

Hi Ashutosh,

I am using fiscal Fiscal year variant in Storage bucket profile.

As I am very new to Macro I do not know how to define layput variable and assigning to original key figure.(please guide me)

And also if there are any book or link available to study macro please suggest me.

This macro is requirement of the client.

Thanks and Regards

Raghavendra Pai