macro: accumulation of KF

[Former Member]

Hello Experts,

i am in the beginning of getting an SAP APO DP/SNP-Consultant and really thankfull for your support.

actually i have a problem in writing the first macros..

my problem is the following one:

"Key Figure 2_accumulation" shoult accumulate the Key Figure 1 data for every month FROM THE PAST and ACTUAL CELL

Do i have to write a macro for every cell for accumulation?! there should be a better way of doing so..

Hope i could make out my problem.

Thanks in advance,

Holger

[Former Member · Accepted solution]

Hi,

use the STEP item to construct a loop and define the time range. Drag a ROW inside the step: it behaves like a CELL, but it iterates through the time range (in the screen, it will show only the first time period, but during execution it will do the iteration from first to last).

regards,

J.

[Former Member · Accepted solution]

Hi James, Thank you for your answer... but i didnt get it..

what do i have to configure? maybe screenshots can help out:

1.Step:  ("Accumulation")

2. Item)      Row: (Result-level - whole time period )

(do i have to mark Processing Direction:In Past?!)

3.-Item: Raw:

following steps?!

I really appreciate your support! Thanks!

[rajkj · Accepted solution]

Hi Holger,

There is an easy method to meet your requirement. Pl check the following screenshots for details.

1. Macro

2. Macro implementation - Just 2 lines, but ensure that your settings of 'SUM function' are same as highlighted in the following screenshot.

Macro Function

3. Results.

(Don't give importance to the key figure headings - I have just used the existing ones to demonstrate the macro)

Thanks,

Rajesh

[rajkj · Accepted solution]

Oops.. it took a while to upload all the images correctly.

[Former Member · Accepted solution]

Hi Rajesh,

wow.. this is fantastic! it works perfectly.. thank you for your effort!!!

--

But according to this there is another upcoming problem: if there are no entries for future periods (empty cell) i dont want this macro to add the last number again and again.. (in my case: data for "Absatzplanmenge kumuliert" and "W 02.2012" (and so on)) should be empty...

Thank`s a lot!!!

Best Regards,

Holger

[rajkj · Accepted solution]

Hi Holger,

You are welcome.

You need to enhance the macro as shown in the following screenshots to skip the blank cells.

Thanks,

Rajesh

[Former Member · Accepted solution]

Hi Rajesh,

there is a new upcoming problem.. 😕

Would be good NOT to have the same data in every cell of raw "IST-Auftragsmenge kumuliert"

Hope u can help out again..

Thanks a lot!

Holger

[Former Member · Accepted solution]

Hi Rajesh,

there is a new upcoming problem.. 😕

Would be good NOT to have the same data in every cell of raw "IST-Auftragsmenge kumuliert"

Hope u can help out again..

Thanks a lot!

Holger

[rajkj · Accepted solution]

Hi Holger,

It looks like the sum macro function settings are not set properly.  Pl check once again.

Thanks,
Rajesh

[Former Member]

Hello APO-Experts!

according to the macro above, the customer does wish to start the accumulation of my key figure for every year new.

So on January each year it has to start at "0".

What is the best way to do so? Do i have to limit the SUM-Macro for every year? JAN201X - DEC201X) or is there a more "elegant" way to fullfill these requirements?

Thank You.

Holger

[rajkj]

Hi Holger,

The easiest way, I could think of using another time series key figure (say TS1) that can hold values of "IST-Auftragsmenge kumuliert" only for the give year. You can define another macro that can copy the values from IST-Auftragsmenge kumuliert to this TS1 key figure on Jan 01 of 201X.  Please check the macro template below.

Step 1: All columns //Zero out the values

TS1[i] = 0

Step 2: Copy only the current year values

(all columns)

IF( DATE(YEAR_BDATE(TODAY)) = TODAY ) // condition to ensure one time execution

for ( i = 1; i < no. of columns ; ++i )  //this simple notation is to represent the step iterations..

IF( DATE(YEAR_EDATE(TODAY)) <> DATE(col. attr of any key fig/column) )

     TS[i] = IST-Auftragsmenge kumuliert[i]

ENDIF

ENDIF

Test this macro and replace the key figure IST-Auftragsmenge kumuliert in your cumulative sum macro with TS1. Finally, setup a background job to execute this macro once in a year.

Thanks,
Rajesh

[Former Member]

Hi Rajesh,

I am sorry, but i dont understand the "body" of this macro at all..

would be very thankful if you coul rebuild this macro (Name of the KF are irrelevant)

i am not working with the macro builder every day so it takes a while (and your help) to geht the macro to work.

maybe this is asking too much, but i would really appreciate it.

Thank You,

Holger

[rajkj]

Hi Holger,

To make it simple, I modified the macro logic. You need to further tweak the following macro template as per your time buckets profile.

Thanks,
Rajesh

[Former Member]

Thank you a lot Rajesh. It works fine now!