Skip to main content
Solved

CALC_THROUGHPUT for two activity columns in the same table

  • June 23, 2023
  • 5 replies
  • 40 views
K
Level 4

Hi,

I would like to to apply this function to mesure the cycle time of Change Activities. The activities table looks like this:

  • Case ID
  • 2 columns "Change From" and "Change To" along with timestamp column
  • Time starts counting for each case when Change To = A for the first time and ends when Change from = A for the last time.

 

 

The logic Im trying to work around is (just disregard the function syntax- Im just trying to explain the logic.

CALC_THROUGHPUT(

First_Occurance ('Change to' = 'A') to

Last_Occurance ('Change from' =' A '),

Remap_Timestamps("Table"."event time", days)

 

What is the right way to implement this logic?

Should I filter the table to be "Change to" = 'A' or "Change from" = A and then merge this two columns to form a single column to use calc_throughput? But then how to merge different columns in the same table?

 

Thank you for any suggestion!

 

 

Best answer by guillermo.gost12

@Abhishek Chaturvedi , I am following your suggestion, targeting the activity column.

 

The thing is there are diffrent reasons for 'Change Delivery Block' in the activity column. Im trying to filter on only reasons that are coded as 'A' in either column 'Change from' or 'Change to'.

I am doing this by remap_values of the Activity column so that those 'Change Delivery Block' will be renamed as 'Enter flow' and 'Leave flow'. But it seems that Celonis cannot recognize those new activity names in the activity column.

 

This is my how I remapped Activity column:

 

CASE WHEN "ACTIVITIES"."CHANGED_TO" = 'ZA'

THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Enter AF'])

ELSE

 CASE WHEN "ACTIVITIES"."CHANGED_FROM" = 'ZA'

 THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Leave AF'])

 END

END

 

I got a new column in my table as the above and applied the cacl_through but Celonis complained that Source Activity [Enter AF] and [Leave AF] cannot be found in activity column "Activities"."Activity"

 

Any ideas how to tackle this?

Hmmm.... I don't see a way to combine your remap with the calc_throughput function:

 

 

image 

only remap that allows is remap_timestamps

 

But... what if instead os using ACTIVITY_NAME you use your column "CHANGED_TO" and "CHANGED_FROM"?

 

I never used it that after the remap timestamp you can state a different column to do the calculation

 

image 

of course, you will need to tweak your activity table to have the info on TO and FROM in the same column, but....

 

HTH

 

P.D: Still I think will be easier with DATEDIFF

5 replies

abhishek.chatu14
Level 11
Forum|alt.badge.img+4

First and Last Occurrence are always going to be activity names and using columns will not help you here. A example to implement this would be as follows

 

Consider a Change Activity XYZ

 

CASE WHEN TABLE.Change to = 'A' AND TABLE.Change to = 'A'

CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'XYZ' ] TO FIRST_OCCURRENCE [ 'XYZ' ]

 

The assumption here is change_to and changed_from has this activity created.

 

G
Level 12

First and Last Occurrence are always going to be activity names and using columns will not help you here. A example to implement this would be as follows

 

Consider a Change Activity XYZ

 

CASE WHEN TABLE.Change to = 'A' AND TABLE.Change to = 'A'

CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'XYZ' ] TO FIRST_OCCURRENCE [ 'XYZ' ]

 

The assumption here is change_to and changed_from has this activity created.

 

As Abhishek said. For non activities, you will need to use DATEDIFF or DAY_BETWEEN (or minutes or...)

Probably you will need also some FIRST and LAST (or PU_FIRST and PU_LAST). Remember you have the clause ORDER BY in those commands.

K
Level 4
  • kinsley.le
  • Author
  • Level 4
  • June 26, 2023

First and Last Occurrence are always going to be activity names and using columns will not help you here. A example to implement this would be as follows

 

Consider a Change Activity XYZ

 

CASE WHEN TABLE.Change to = 'A' AND TABLE.Change to = 'A'

CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'XYZ' ] TO FIRST_OCCURRENCE [ 'XYZ' ]

 

The assumption here is change_to and changed_from has this activity created.

 

@Abhishek Chaturvedi , I am following your suggestion, targeting the activity column.

 

The thing is there are diffrent reasons for 'Change Delivery Block' in the activity column. Im trying to filter on only reasons that are coded as 'A' in either column 'Change from' or 'Change to'.

I am doing this by remap_values of the Activity column so that those 'Change Delivery Block' will be renamed as 'Enter flow' and 'Leave flow'. But it seems that Celonis cannot recognize those new activity names in the activity column.

 

This is my how I remapped Activity column:

 

CASE WHEN "ACTIVITIES"."CHANGED_TO" = 'ZA'

THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Enter AF'])

ELSE

 CASE WHEN "ACTIVITIES"."CHANGED_FROM" = 'ZA'

 THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Leave AF'])

 END

END

 

I got a new column in my table as the above and applied the cacl_through but Celonis complained that Source Activity [Enter AF] and [Leave AF] cannot be found in activity column "Activities"."Activity"

 

Any ideas how to tackle this?

G
Level 12

@Abhishek Chaturvedi , I am following your suggestion, targeting the activity column.

 

The thing is there are diffrent reasons for 'Change Delivery Block' in the activity column. Im trying to filter on only reasons that are coded as 'A' in either column 'Change from' or 'Change to'.

I am doing this by remap_values of the Activity column so that those 'Change Delivery Block' will be renamed as 'Enter flow' and 'Leave flow'. But it seems that Celonis cannot recognize those new activity names in the activity column.

 

This is my how I remapped Activity column:

 

CASE WHEN "ACTIVITIES"."CHANGED_TO" = 'ZA'

THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Enter AF'])

ELSE

 CASE WHEN "ACTIVITIES"."CHANGED_FROM" = 'ZA'

 THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Leave AF'])

 END

END

 

I got a new column in my table as the above and applied the cacl_through but Celonis complained that Source Activity [Enter AF] and [Leave AF] cannot be found in activity column "Activities"."Activity"

 

Any ideas how to tackle this?

Hmmm.... I don't see a way to combine your remap with the calc_throughput function:

 

 

image 

only remap that allows is remap_timestamps

 

But... what if instead os using ACTIVITY_NAME you use your column "CHANGED_TO" and "CHANGED_FROM"?

 

I never used it that after the remap timestamp you can state a different column to do the calculation

 

image 

of course, you will need to tweak your activity table to have the info on TO and FROM in the same column, but....

 

HTH

 

P.D: Still I think will be easier with DATEDIFF

K
Level 4
  • kinsley.le
  • Author
  • Level 4
  • June 28, 2023

@Abhishek Chaturvedi , I am following your suggestion, targeting the activity column.

 

The thing is there are diffrent reasons for 'Change Delivery Block' in the activity column. Im trying to filter on only reasons that are coded as 'A' in either column 'Change from' or 'Change to'.

I am doing this by remap_values of the Activity column so that those 'Change Delivery Block' will be renamed as 'Enter flow' and 'Leave flow'. But it seems that Celonis cannot recognize those new activity names in the activity column.

 

This is my how I remapped Activity column:

 

CASE WHEN "ACTIVITIES"."CHANGED_TO" = 'ZA'

THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Enter AF'])

ELSE

 CASE WHEN "ACTIVITIES"."CHANGED_FROM" = 'ZA'

 THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Leave AF'])

 END

END

 

I got a new column in my table as the above and applied the cacl_through but Celonis complained that Source Activity [Enter AF] and [Leave AF] cannot be found in activity column "Activities"."Activity"

 

Any ideas how to tackle this?

Dear @Guillermo Gost : I have finally figured out how to calculate throughput time based on the modified acitivity column, thanks to the your suggested Calc_Throughput extented function which I was not aware of before. I simply remap_values the acitivity column in the "green" part of the function.

 

I will drop my code here, in case it can be helpful to anyone else. The part in italic is where I remapped the Acitivity column.

 

CALC_THROUGHPUT (

  FIRST_OCCURRENCE['Enter AF'] to LAST_OCCURRENCE ['Leave AF'], REMAP_TIMESTAMPS ("Activities"."Acitivity", days),

 

CASE WHEN "ACTIVITIES"."CHANGED_TO" = 'A'

THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Enter AF'], ['Set Delivery Block', 'Enter AF'], ['Set Initial Delivery Block', 'Enter AF'])

ELSE

  CASE WHEN "ACTIVITIES"."CHANGED_FROM" = 'ZA'

  THEN REMAP_VALUES("ACTIVITIES"."ACTIVITY", ['Change Delivery Block', 'Leave AF'], ['Remove Delivery Block', 'Leave AF'])

  END

END

 

 )

 

Community Code of ConductAccessibility statement