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 that’s 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

1 Like

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, it’s because the target column can’t 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

1 Like

Great thanks - the first option works fine :slight_smile: