Key Figure calculation Issue

[pallavidwivedi108]

Hello All,

I am trying to configure the below flow using Key figure calculations. All Key figures are defined at the same Base Planning Level.

KF1 is a stored and System Editable Key Figure.

KF2 defaults to KF1 and is "Editable in the Current and Future"

KF3 is a stored Key figure.

KF4 is a calculated Key Figure ( KF4 = KF3 * KF1)

I have configured KF5 as--> If KF2 is null, then KF 5 = KF4 + KF1 , else KF2.

However while testing the above calculation, the addition of KF4 + KF1 is not reflected in KF5 when KF2 is null. If KF2 has a value (edits are made in KF2 and saved), then KF5 reflects the correct value.

Example-->

Case 1 :
KF1 = 100
KF 2 = 200 (manually edited and saved)
KF3 = 0.2

KF4 = 20 (calculated based on KF formula)
KF 5 = 200 (as expected)

Case 2:

KF 1 = 100

KF 2 = 100 (defaults to KF 1, no manual edit)

KF 3 = 0.2

KF 4 = 20 (calculated based on KF formula)

KF 5 = 100 ( expected value here is 100 + 20 = 120 )

We get the expected results if the addition of KF 1 and KF 4 is copied to a stored Key figure using a Copy Operator. And then the stored Key Figure is used in the calculation of KF5. However we do not want to use a operator in our design. Is there any way to have the above scenario with Key figure calculation only?

Thanks in advance !

[former_member367912]

Hi Pallavi,

Use can use the below approach which is slight modification to yours and give output as per your requirement.

In your existing design

1> Create a new KF6@PL with below details(Its basically copy of ur KF2) (JUST Calculated KF)

AKKF6@REQUEST = SUM( "AKKF6 @WKPRODLOC" ) ( NOT STORED)

AKKF6@WKPRODLOC = "AKKF2@WKPRODLOC" (Stored and Input)

2> Use this new KF6 in KF5 calculation. Rest all remains same.

AKKF5@WKPRODLOC = IF(ISNULL( "AKKF6@WKPRODLOC" ), "AKKF1@WKPRODLOC" + "AKKF4@WKPRODLOC" , "AKKF6@WKPRODLOC" )

If you need screenshots can share the same. I guess since the Question is closed so I am not able to uplaod the screenshot.
I have configured the same and tested its working.

Regards,

Akhilesh Agarwal

[pallavidwivedi108]

Hi Akhilesh,

Thanks for your response. I haven't checked with your calculation in the system. In your calculation of KF5, I am assuming KF6 is marked as calculated. Which means that essentially it will be checking for KF2 only in the isnull condition. So what do you think is the reason why it is working in your configuration and not mine? Is there something that i am missing here ?

Thanks,

Pallavi

[former_member367912]

Hi Pallavi,

I could not see any logical reason why yours not working and why mine worked.What I tried was using your design with slight modification the best way we can do that. But fact is even when I replicated your design in my system even I faced the same issue so that means its DEFINITELY NOT related to any data, planning object or system specific.

1> Basically KF6 is Pure calculated KF and due to below calculation it will only hold values for the periods in which KF2 is Manually edited and for rest it will be none.

AKKF6@WKPRODLOC = "AKKF2@WKPRODLOC" (Stored and Input).

2> So now when we use this KF6 in KF5 calculations it works as expected.

Kindly check and confirm once.

Issue is since you have closed this thread (assuming) so I am not able to attach screenshots. Let me know if you need any other details.

Regards,

Akhilesh Agarwal

[pallavidwivedi108 · Accepted solution]

Hi Rohit,

The screenshots were taken at a later stage while i was trying some combinations and hence the second calculation is inactive. Post activation, i checked every calculation that all steps were active and also went through all warnings during activation to find out any probable cause for not getting the expected result.

The only warning regarding KF2 that i received was :

Calculation KF5@PL: Calculation for KF2 exists, but stored value is used

I didn't however make any change to this since my intent was to utilize the stored value of KF2.

With regards to the calculation graph, it as generated correctly or as expected post the activation.

At this point, i have changed the calculation of that KF and used CASE to get the expected output. I don't think raising an OSS would make sense at this point since i have removed those calculations altogether. Do let me know your thoughts on this.

Thanks,

Pallavi

[former_member367912 · Accepted solution]

Hi Pallavi,

Use can use the below approach which is slight modification to yours and give output as per your requirement.

In your existing design

1> Create a new KF6@PL with below details(Its basically copy of ur KF2) (JUST Calculated KF) AKKF6@REQUEST = SUM( "AKKF6 @WKPRODLOC" ) ( NOT STORED) AKKF6@WKPRODLOC = "AKKF2@WKPRODLOC" (Stored and Input)

2> Use this new KF6 in KF5 calculation. Rest all remains same.

AKKF5@WKPRODLOC = IF(ISNULL( "AKKF6@WKPRODLOC" ), "AKKF1@WKPRODLOC" + "AKKF4@WKPRODLOC" , "AKKF6@WKPRODLOC" ) If you need screenshots can share the same. I guess since the Question is closed so I am not able to uplaod the screenshot.
I have configured the same and tested its working. Regards,

Akhilesh Agarwal

[pluthaman]

Hi Pallavi,

In your initial example,

Example-->

Case 1 :
KF1 = 100
KF 2 = 200 (manually edited and saved)
KF3 = 0.2

KF4 = 20 (calculated based on KF formula)
KF 5 = 200 (as expected)

Case 2:

KF 1 = 100

KF 2 = 100 (defaults to KF 1, no manual edit)

KF 3 = 0.2

KF 4 = 20 (calculated based on KF formula)

KF 5 = 100 ( expected value here is 100 + 20 = 120 )

in Case 2, on KF 5, you mentioned it should have got 120, instead it is getting 100. But isn't 100 correct, because KF5 logic is - If KF2 is null, then KF 5 = KF4 + KF1 , else KF2.

KF2 isn't null as it gets value from KF1, which is 100, so Case 2's KF 5 value of 100, seems correct.

Or, I am completely missing something here.

[pallavidwivedi108]

Hi Uthaman,

KF2 defaults to KF1 and is an editable Key Figure. This means that in the background, it doesn't have any values. Only when a manual edit is made to KF2 and saved, then that value is stored for KF2. Rest other periods, it is a calculated value, and hence shows null or empty cell in the backend. You can verify this using "Master Data Workbook" option for any defaulting and editable Key figure.

So in Case 2, since there is no manual edit that is made, KF2 also shows value as 100. But this 100 is not saved in KF2, it is a calculated value of KF2 which is coming from KF1. I hope this clarifies the doubt.

Thanks,

Pallavi

[former_member242371]

Hi Pallavi,

The calculation looks correct except that i could see second calculation in KF5 is not active in your screenshot.

If you have activated your planning area and this calculation step is not generated try checking the activation log for any warning related to KF5. Additionally also check the calculation graph of KF5 to see if it is generated properly.

If none of these works, you can submit an OSS for detailed analysis in the system.

Thanks,

Rohit

[pallavidwivedi108]

kf1.png

kf2.png

kf3.png

kf4.png

kf5.png

Hello Sai, Rohit,

I have uploaded the Key figure calculation for all the Key Figures. Request you to let me know if i am missing anything

Thanks,

Pallavi

[limbagiri]

Hello Pallavi,

For me everything seems fine. You have masked dis-aggregation expression in KF2, May I know what does it do ? Also can you wipe KF2 values for sample PROD-LOC-CUST combination using Create Master Data workbook option ("Planning Objects with KF Data" option) in Excel and check.

Regards

Sai Samrat

[pallavidwivedi108]

Hi Sai,

Tried clearing the values from backend. It still wasnt working.

Have changed the Key Figure calculation and used "CASE". Its working now. Thank you for you help in this.

Updated calculations are as below-->

TEST = if (isnull(KF2 @PL),100,500)

INTERMEDIATE @PL = KF1 @PL + KF4@PL

KF5= CASE( "TEST @PL" , 100, "INTERMEDIATE @PL" , "KF2@PL")

Regards,

Pallavi

[limbagiri]

Hello Pallavi,

I hope you must have defined KF2 as IF(ISNULL( "KF2@PL" ), "KF1@PL" , "KF2@PL" ) which means KF2 is Stored and Calculated KF. So in KF5, you want calculation to be KF4 + KF1 when there is no value explicitly entered in KF2 else pick entered value. Which means you need to include KF2 as stored (not calculated) in KF5 calculation. click on input key figures and tick the stored for KF2.

This should solve your problem. Kindly try this one and let me know the outcome.

Regards

Sai Samrat

[pallavidwivedi108]

Hi Sai,

Thank you for providing your inputs on this,

Yes, KF2 is marked as Stored in the calculation of KF5. Still the calculation doesn't give the expected result.

Couple of other alternatives that i have tried-->

1. KF4 + KF 1 is calculated in a INTERMEDIATEKF and INTERMEDIATEKF is used in calculation of KF5.

2. Used a Helper Key figure for calculating the Sum of KF4 and KF1 and used the Helper in KF5 calculation

Both the above options don't provide the expected solution. Till now only the Copy Operator has yielded the expected outcome, but we don't wish to go with the operator approach.

Let me know if i have missed anything or if there is another approach that i can try.

Thanks !

[former_member242371]

Hello Pallavi,

It seem in KF5 calculation you have entered KF2 as calculated input instead of stored. Can you check this again?

Thanks,

Rohit

[pallavidwivedi108]

Hi Rohit,

I re-verified my calculation. KF2 is marked as stored in KF5 calculation.

Thanks,

Pallavi

[limbagiri]

Hello Pallavi,

This is very standard calculation. May be we are making mistake unknowingly. Could you paste KF2 and KF5 screenshots here (you can mask names. Just wanted to see calculations). Also try to cleanup the data and test with new set of data.

Thanks

Sai Samrat

[pallavidwivedi108]

Hi Sai,

Thank you for going through my Key figure calculation. I have attached the screenshots of the configuration as a separate comment. Please let me know if i did anything wrong.

Thanks,

Pallavi