Skip to main content

Need to calculate time spent on Credit hold activity for order to cash process and populate a column of OLAP table but credit hold activity is denoted by 2 names in activity table 1)Set Credit Hold 2)Set Default Credit Hold.What should be the formula for this

I have used formula

(CALC_THROUGHPUT(FIRST_OCCURRENCE[Set Default Credit Hold] TO LAST_OCCURRENCE[Release Credit Hold], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".EVENTTIME, HOURS)))

But I need to put below condition as well for same column

(CALC_THROUGHPUT(FIRST_OCCURRENCE[Set Credit Hold] TO LAST_OCCURRENCE[Release Credit Hold], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES".EVENTTIME, HOURS)))

Hi @minakshi2112,
welcome to the Celonis Community!
You can map both credit hold activity names to one name, and then pass those to the CALC_THROUGHPUT function in the last argument. In your case, it would look like this:
CALC_THROUGHPUT(FIRST_OCCURRENCE['Set Credit Hold'] TO LAST_OCCURRENCE['Release Credit Hold'], REMAP_TIMESTAMPS("_CEL_O2C_ACTIVITIES"."EVENTTIME", HOURS),
REMAP_VALUES("_CEL_O2C_ACTIVITIES"."ACTIVITY", ['Set Default Credit Hold', 'Set Credit Hold'])
)

In that case, I map all Set Default Credit Hold activities to Set Credit Hold. When passing this in the last argument of CALC_THROUGHPUT, CALC_THROUGHPUT will use that column instead of the original activity column. Since now all credit hold activities are called Set Credit Hold, all credit hold activities are now taken into account when you write FIRST_OCCURRENCE['Set Credit Hold'].
There is a similar example available in the documentation. Search for the page on Throughput Times and take a look at example [5].
Cheers
David
Thanks a lot. It worked.

Reply