Alert Key figure to display Flag only for 3 months in past and 3 months in future in planning view

[former_member666725]

Hi IBP Experts,

I have a requirement on Alert Key figure.

1 ) Written the below expression which will display '1' or '0' for all the periods in the past and in the future in RED color depending on the condition.

ALRTFCSTDIFFSALES@REQUEST = IF(ABS(("ADJUSTEDACTUALSQTY@REQUEST" - "DLYAVGFINALFCST@REQUEST")/"DLYAVGFINALFCST@REQUEST")*100 > 30, 1, 0)

2 ) Requirement is

Alert Key figure need to display "Alert Flag" (Ex: 1) only for 3 months in the past and 3 months in future in the planning view.

Developed an expression like below at REQUEST level.

ALRTFCSTDIFFSALES@REQUEST = IF ((“PERIODID3” <= "$$PERIODID3CU$$" – 3) OR (“PERIODID3” > "$$PERIODID3CU$$" + 2), NULL, IF(ABS(("ADJUSTEDACTUALSQTY@REQUEST" - "DLYAVGFINALFCST@REQUEST")/"DLYAVGFINALFCST@REQUEST")*100 > 30, 1, 0)))

3 ) But PERIODID condition check at REQUEST level will not work because REQUEST planning level is not having PERIOD.

4 ) Attached images for your reference.

Need your suggestions.

alertforecastdiffsales-kf-flag.jpgalertforecastdiffsales-kf-flag-expression.jpgalertforecastdiffsales-kf-flag-expression-required.jpg

Thanks

Appreciate your help and inputs in advance.

[Irmi_Kuntze · Accepted solution]

I suggest to move the formula to te base planning level, and have request level as for example MAX of that, because when on aggregated level you want to see an alert when on any detailed level the condition for alert is given

Just think what you would want to see if the user displays data on quarterly level, because PERIODID3CU is likely in the middle of a quarter I most cases, and average accordingly

An alternative could be to have helper in-between where you copy the values of the input kf into, but only within the given periods on PERIODID3-level. Than do the formula on request level, but stripp off the PERIODID3-clause

[former_member666725]

Hi Irmhild Kuntze,

Thanks for your reply.

I have tried writing the formula to the base planning level (MTHPRODLOCCUSTSBU), and using aggregation function MAX and AVG and MIN, I tried to get the value at request level.

But I am getting different results at Base Planning level(MTHPRODLOCCUSTSBU) and different results at REQUEST level

1 ) PERIODID3 = Month

Users will be seeing data at monthly level to check alerts.

2 ) I have attached screenshots where either '1' or '0' will be displayed if the difference between "Actual sales" and "final Forecast" is ore than 30%.

3 ) Requirement is

Alert Key figure need to display "Alert Flag" (Ex: 1) only for 3 months in the past and 3 months in future in the planning view.

Developed an expression like below at REQUEST level.

4 ) Could you please give some sample expression syntax on how to check PERIODID3 condition to display Alert Flag '1' or '0' only for past 3 months and future 3 months.

Request level, system will not allow to check PERIODID condition because REQUEST planning level is not having PERIOD.

5 ) How to check the below condition

IF ((“PERIODID3” <= "$$PERIODID3CU$$" – 3) OR (“PERIODID3” > "$$PERIODID3CU$$" + 2

and also check the below condition

IF(ABS(("ADJUSTEDACTUALSQTY@REQUEST" - "DLYAVGFINALFCST@REQUEST")/"DLYAVGFINALFCST@REQUEST")*100 > 30, 1, 0)

and display '1' or '0' for the KF "Alert: Forecast Difference from Sales" - (ALRTFCSTDIFFSALES).

Thanks in advance.