Macro question

[Former Member]

I am trying to create a Macro which has 2 KFs. The way it should work is:

If there is a specific values value in KF1(lets say values 100) in any of the monthly buckets(cells) in the last 6 months , it should go back 6 months in time for KF 2 and perform an arithmatic operation like summing up for the last 6 months .

Summing up is not an issue, the issue is starting from 6 months in the past. what the macro is doing currently is whereever its finding the value in KF1 (a 100), it is starting the operation from that same period for KF2 only.

But Irrespective of where the value is in the last 6 months for KF1, I want it to always go 6 months in the past for KF2.

Any help is appreciated.

Thanks,

AA

[Former Member]

Hi Alicia,

I had a similar kind of problem where I wanted to take the sum of two key figures in the current period and write it to a third key figure, 36 months in the past. It kept writing the value to the current month until I changed the attributes for the destination row. I had to change the write area to "Whole Table".

If I double click the destination row in the row in macro builder, my attributes are set to this:

Column Offset: 12/31/2006 (36 months in the past)

Change mode: Value Change

Write Area: Whole Table

Outside Wr. Area Write in Boundary Vals

Write protection: Overwrite Existing Values

Calculation Error No Value

I am running on APO 5.0, SP13

Hope this helps,

Dan

[Former Member]

Thanks for your response. But in my case the write area is just one cell/one period which is the current bucket.

The issue is with READ AREA, and I cant make it read always 60 months in the past whenever it finds a single cell value 100(appreas only once) in any of the 60 past buckets for the first KF. For this Ive tried changing the processing area in the past /user defined/total at the STEP level in the Macro. And I used the AREA of past 60 months for the 2nd KF and used SUM function.

Still it reads from the coloum where it finds 100 and not from 60 months in the past.

I want the 60 months for KF2 to be default, and use the value 100 (from any one single bucket out of 60 in the past) from KF1 as a trigger only.

Thanks

[Former Member]

Hi Alicia,

I'm not sure how to do what you're describing using areas, but let me take a stab at this just by processing one row and column at a time.

Step 1: 6 iterations, from July 1 2009 to December 1 2009.

If the value in KF1 = 100.

Set a layout variable called 'ALICIAS_VAR' to 1.

Exit the loop using the macro function STEP_CALC_STOP

Else.

End If.

Step 2: 1 iteration, July 1 2009 (six months ago)

If the layout variable 'ALICIAS_VAR' = 1.

KF2 = Sum of area July 1 to December 1

Else.

End If.

I would use the STEP_CALC_STOP function so that as soon as the macro finds the first example of that value it will stop processing through every column of the row, hopefully this will help performance.

Dan