Macro to assign zero values to a Key Figure

[Former Member]

Hi All,

I am still learning APO so am struck in one simple macro...

My requirement is if the result of calculation is negative then assign 0 value else the result to a Key Figure.

I have written a macro as below... Please share your experiences with me...

STEP - Calculation

  IF

IS_INITIAL ( KF1) <> 1

    Row KF2 = KF1-KF3

    Else

   Row KF2= KF4 - KF3

   End IF

IF

KF2 < 0

KF2= 0

End IF

I know there is some mistake in this logic not able to think what I should do.

My requirement is if the resultant KF2 is negative then assign 0 value to it else keep the value getting in the subtraction formula.

Thanks in advance,

Regards,

Prabhat

[RahulHanda · Accepted solution]

Prabhat

Assuming you are comparing two key figures and writing results in third,

Write this:

New macro 11

     New step : ( 78 Iterations : W 36.2015; W 08.2017 )

          IF

          New condition

               (

               Row: Key Figure 1 ( Frm  W 36.2015 )

               -

               Row: Key Figure 2 ( Frm  W 36.2015 )

               ) < 0

          Row: Key Figure 3 ( Frm  W 36.2015 ) =

               0

          ELSE

          Row: Key Figure 3 ( Frm  W 36.2015 ) =

               Row: Key Figure 1 ( Frm  W 36.2015 )

               -

               Row: Key Figure 2 ( Frm  W 36.2015 )

          ENDIF

Follow if this already answered your question

Regards

Rahul

[Former Member · Accepted solution]

Thank you Rahul for your help.

Please be informed that there are 4 key figures in question but you have taken only 3 key figure in your calculation.

Let me put my logic in words instead of using syntax....

If Key Figure 1 in planning book has some value

then

Key Figure 2 = Key Figure 1-Key Figure 3

but if Key Figure 1 is blank in planning book then use other Key Figure "Key Figure 4" to do the subtraction

so

Key Figure 2 = Key Figure 4 - Key Figure 3

Now since we don't want negative values so If the value of Key Figure 2 we got in above formula is negative then assign 0 to it else keep the value.

If Key Figure 2 < 0

then

Key Figure 2 = 0

Else

Key Figure 2 = keep the calculated value from above subtraction formula.

Please let me know if you have any more question.

Regards,

Prabhat

[Former Member · Accepted solution]

Prabhat,

If your issue is with the second part not working. i.e., negative values are not replaced with zero,

That's because both the IF conditions are in the same step as per what you have mentioned.

Write the second IF condition in new step.

See the attached screenshot for the similar Macro I have created for your scenario.

If there is any other specific issue, please mention.. I shall help you with the same.

Regards,

Hussain

[Former Member · Accepted solution]

If you want to use IS_INITIAL function.

You should compare with Row attributes. i.e., data source should be Row Attributes for Actual (KF1)

Also find the attached screenshot with IS_INITIAL(KF1) <> 1.

This covers 2 ways of writing Macro for your requirement. Do let me know if you still need any help.

Kindly close the discussion if resolved. 

Regards,

Hussain

[Former Member · Accepted solution]

Hi Hussain,

Thank you for your help. Yes you are right that the negative values are not getting replaced with zero.

Your approach is right to have 2 step macro but there is one issue which I found.

With two step also, the negative values were not getting replaced with 0 and this may be because by the time the control reaches to the 2nd step it had already assigned the negative value to the KF in the 1st step.

And since the negative values are not allowed,it throws an error.

Regards,

Prabhat

[Former Member · Accepted solution]

Prabhat,

There should not be any errors if you have followed above 2 suggestions correctly.

Please share the error message details and also the screenshot of macro if possible.

Kindly close the discussion if already resolved

Regards,

Hussain

[Former Member · Accepted solution]

Hi,

Thank you for your support.

I have attached the screenshot of the macro.

I used the concept of Auxiliary row to store the calculated value in step 1 and assigned the value to the KF based on condition and is currently in testing.

Thanks a lot for your help.Your suggestion to use 2 step macro worked for me.

Regards,

Prabhat

[Former Member · Accepted solution]

Dear Prabhat,

That's good to know..

Directly using the Keyfigure or Auxiliary Keyfigure.. In both the cases Macro logic will work.

Your mentioned Negative values error is because Your Output keyfigure does not support negative values.

In the Planning Area - Key figure settings, due to negative values not allowed indicator for the affected key figure.

You can either remove the indicator or use Auxiliary KF as you already did.

If you have any further queries in Macros you can approach me any time.

Kindly close the discussion if resolved.

Regards,

Hussain

Message was edited by: Hussain Mohammad

[Former Member · Accepted solution]

Just FYI - in PA the Key Figure setting is allowed to accept negative value so we should not get error but however the issue is resolved using auxiliary so closing the thread with full credit to you.

[Former Member]

Dear Prabhat,

Please verify if you are getting any syntax errors before activation.

If it is allowing you for activation there can be errors in Keyfigure names mentioned.

Also, there is a possibility that your IS_INITIAL function is not working..

Try with IF KF1 = 0 instead of IS_INITIAL function. This should resolve your issue.

Share the screenshot of the Macro mentioned if it still does not resolve your issue..

Regards,

Hussain

Message was edited by: Mohammad Hussain