Macro quick check..negative values

[Former Member]

Dear Macro Experts

I have done this syntactically correct 2 step macro. I have seen similar threads where either questions were open ended or replies were.

So posting my version.

Step1

Result row  = Average of some periods of another KF in same planning book

Step2

If

Result row < 0

Set Result row = 0

endif

This macro is ZEROing out everything in the result row at any level of drill down/up

Example

Another key figure series: M1=20, M2=30, M3=40, M= 55

Result row = Average 3 periods M3- 20 + 30 + 40 divide 3 = 30, M4 = 30+40-55/3 = 5

Now I expect M4 to be equal to 0 is the same result row whatever be the CVC levels loaded and saved. BUT I see all values in result row set to 0. Tried the macro test utility setting this as defaut, level change and directly executable macro but in all cases I see only zero

Question: Where am I going wrong on such simple syntax

Scrshot attached

Prod. fix issue. Quick response appreciated.

Guru

[former_member209769 · Accepted solution]

Hi Guru,

You are making the 3 months average as zero first, and then doing a check for negative quantity without doing anything between the IF and ENDIF statements.

The first step for calculating the average for 3 months should be ok.

In the next step, have

IF

3 months average <0

3 months average = 0.

ENDIF.

So, basically you need to put the statement where you put the statement of "3 months average = 0" after the check in IF, but before the ENDIF.

Thanks - Pawan

[Former Member · Accepted solution]

Thanks Pawan,

Much needed help. Many thanks. The zeroing is fixed now but I still see negative values in the same result row. i.e. the main objective is-  NO Matter what level of aggregation the data view in, the value of the result must be 0. I have looked into several other macros that are probably influencing this result but the non-negative condition is something I introduced.

Not sure if this context is enough for you to answer this. May be I spend more time finding the root cause and then come back. You may like to offer some generic reasons.

Thanks

Guru

[former_member209769 · Accepted solution]

Hi Guru,

Tough to comment without knowing more details, but some initial comments:

1) Unless your users would be manually making changes to data and want to do some calculations real-time, it makes sense to have the calculations in background.

So, don't have ANY of your macros as either default, level-change, start or exit. This would mean that even if users would make any changes to the data by going to planning view, they would be seeing the impact and any relevant changes only after the relevant jobs containing macros run. This way you would be sure that no macro is making any changes when someone comes to planning view, but yes, this is not possible in all the scenarios.

2) Let me assume that you ran the macro for all the CVCs (at some aggregate level). Unless you can think of some issue, run the above macro at the most detailed level of  aggregation. This would ensure that when you are viewing the data at any aggregate level, the 3 months average cannot be negative. Otherwise if you change the value of 3 months average at a higher level from negative to zero, but at some detailed level, you have negative values for 3 months' average, then with disaggregation from higher to lower level, it's possible to still see negative values at detailed level.

Thanks - Pawan

[Former Member · Accepted solution]

Thanks Pawan,

Much appreciate your response that I could see only now. Yes I did run the macro at the most granular CVC's. I have managed to fix the negative qty issue. As such the macros are set to default but some input key figures are editbale but macro result KF's are output only. I was using  the test fucntionality to verify the results. This is for planners to do some simulations before saving results.

Thanks

Guru