Question
Amount of price change in Euro
Hi all,
When analyzing price changes in Celonis I would like to show in our details tab the Euro amount of the actual price change per PO line. We are already using quite a sophisticated formula to show in a graph the price changes for different thresholds in percentage. I assume I can use this formula partially in order to show the Euro amount of the price change. Following part, I used to do that (which does not work yet):
CASE
WHEN ISNULL(PU_FIRST(EKPO,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN, _CEL_P2P_ACTIVITIES.ACTIVITY_EN = Change Price)) = 1 THEN No Price Change Activity
WHEN PU_FIRST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_FROM_FLOAT,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN = Change Price) -
PU_LAST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_TO_FLOAT,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN = Change Price)
END
Any idea how to resolve?
Thanks, Oli
Hey Oli,
One word is all it takes.
The first part of your statement is correct, checking whether or not there is a price change at all, although I personally prefer to check whether PU_COUNT > 0 rather than ISNULL(PU_FIRST()) but I guess thats just me.
The second part seems like a really good performing nice solution, just you used WHEN instead of ELSE, so here is the (hopefully correct) solution to your problem:
CASE
WHEN
ISNULL(PU_FIRST(EKPO,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN, _CEL_P2P_ACTIVITIES.ACTIVITY_EN = Change Price)) = 1
THEN No Price Change Activity
ELSE
PU_FIRST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_FROM_FLOAT,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN = Change Price) -
PU_LAST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_TO_FLOAT,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN = Change Price)
END
Best Regards,
Benedict
Oli:
CASE
WHEN ISNULL(PU_FIRST(EKPO,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN, _CEL_P2P_ACTIVITIES.ACTIVITY_EN = Change Price)) = 1 THEN No Price Change Activity
WHEN PU_FIRST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_FROM_FLOAT,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN = Change Price) -
PU_LAST(EKPO, _CEL_P2P_ACTIVITIES.CHANGED_TO_FLOAT,"_CEL_P2P_ACTIVITIES".ACTIVITY_EN = Change Price)
END
Thanks for the fast reply. Unfortunately, I receive following error message when using the proposed formula:
Error in formula --> Operator implementation could not be found or wrong types. Operator CASE is not compatible with cases of type: STRING and DOUBLE.
Any ideas how to resolve?
Yes, its because the target column cant be both data types: STRING and DOUBLE but your CASE WHEN statement either gives out a string or a double. You have two possible approaches: Either Change No Price Change Activity to NULL or convert the double to a string.
Depending on what you want to achieve I would recommend the first option.
Best Regards,
Benedict
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.