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
Page 1 / 1
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
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?
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
Depending on what you want to achieve I would recommend the first option.
Best Regards,
Benedict
Great thanks - the first option works fine 
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.