Skip to main content
Question

Combining PU Function & CALC_CROP Function

  • February 22, 2024
  • 2 replies
  • 12 views
henning.gertz
Level 3
Forum|alt.badge.img

Hi,

is there a way to combine a PU Function with a CALC_CROP Function?

 

CASE WHEN PROCESS # 'Change Requested Delivery Date'

THEN

TO_TIMESTAMP ( PU_FIRST ( "VBAP" , "_CEL_O2C_ACTIVITIES"."CHANGED_TO" , "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" = 'Change Confirmed Delivery Date')

, 'YYYYMMDD' )

ELSE

"VBEP"."EDATU"

END

 

This is the formula without CALC_CROP. But doing this, Celonis is looking at the first "Change Confirmed Delivery Date".

I would like to look at the first "Change Confirmed Delivery Date" Event after the last "Change Requested Delivery Date" Event.

2 replies

L
Level 0
Forum|alt.badge.img+6
  • lea.hanna12
  • Level 0
  • March 8, 2024

Hello Henning,

Thank you for your question. 

 

Yes it is possible to use a CALC_CROP function within a CASE WHEN! At first glance I believe you are looking for something like this: ( Quick note: the TO_TIMESTAMP() function is deprecated, hence I have replaced it with TO_DATE() instead )

 

CASE WHEN

CALC_CROP(LAST_OCCURRENCE['Change Requested Delivery Date']

TO FIRST_OCCURRENCE['Change Confirmed Delivery Date'], "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN")

IS NOT NULL

THEN

TO_DATE(PU_FIRST("VBAP", "_CEL_O2C_ACTIVITIES"."CHANGED_TO",

"_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" = 'Change Confirmed Delivery Date'),  FORMAT('%Y-%M-%D')

)

ELSE "VBEP"."EDATU" END

 

However, I would need more context if possible please of what you are trying to achieve with the overall function to be sure. Please let me know if the above is not what you are looking for.

 

Thank you!

henning.gertz
Level 3
Forum|alt.badge.img

Hello Henning,

Thank you for your question. 

 

Yes it is possible to use a CALC_CROP function within a CASE WHEN! At first glance I believe you are looking for something like this: ( Quick note: the TO_TIMESTAMP() function is deprecated, hence I have replaced it with TO_DATE() instead )

 

CASE WHEN

CALC_CROP(LAST_OCCURRENCE['Change Requested Delivery Date']

TO FIRST_OCCURRENCE['Change Confirmed Delivery Date'], "_CEL_O2C_ACTIVITIES"."ACTIVITY_EN")

IS NOT NULL

THEN

TO_DATE(PU_FIRST("VBAP", "_CEL_O2C_ACTIVITIES"."CHANGED_TO",

"_CEL_O2C_ACTIVITIES"."ACTIVITY_EN" = 'Change Confirmed Delivery Date'),  FORMAT('%Y-%M-%D')

)

ELSE "VBEP"."EDATU" END

 

However, I would need more context if possible please of what you are trying to achieve with the overall function to be sure. Please let me know if the above is not what you are looking for.

 

Thank you!

Thanks for your answer.

I already found the correct solution :)

Community Code of ConductAccessibility statement