I have 2 columns with timestamps and I need difference between those timestamps as 3rd column and I need a single KPI as avg(3rd Column).Can anyone suggest the pql formula for that..?

Hi,

for the 3rd column you can make use of one of those DateTime Difference Functions: DateTime Difference (celonis.com)

e.g like DAYS_BETWEEN or DATEDIFF

And you KPI can be calculated like this:

AVG(DAYS_BETWEEN(

     PU_FIRST(CASE_TABLE, TABLE.COLUMN1),

     PU_LAST(CASE_TABLE, TABLE.COLUMN2)

    )

)

BR

Dennis

Hi Dennis,

Both timestamps are in case table then the formula which you suggested will not work right.

is there any other formula for that..?

Ok in this case I would try to make use of an DOMAIN_TABLE: DOMAIN_TABLE (celonis.com)

e.g:

AVG(DAYS_BETWEEN(

   PU_FIRST(DOMAIN_TABLE(CASE_TABLE._CASE_KEY), CASE_TABLE.COLUMN1),

   PU_LAST(DOMAIN_TABLE(CASE_TABLE._CASE_KEY), CASE_TABLE.COLUMN2)

  )

)

BR

Dennis

Hi Dennis,

The use of DOMAIN_TABLE has worked to solve that.

Thank you so much for your time.

You're welcome. :)