Skip to main content
Question

Hi Celopeers, This question is for 2 reasons. 1.how to filter out few activities from my activity table & get the Activity_lag value properly even after filtering the activities 2) A scenario based question about DateDiff function

  • February 16, 2023
  • 1 reply
  • 15 views
S
Level 3

Hi Celopeers,

In My Activity table , 1 case has 10 Activities. I am creating a report in which I need only 6 out of this 10 Activities. So, I introduced a Compound Filter and restricted the not-required 4 activities. Now, I was asked to find out the time taken between activities. I used the PQL query 'DATEDIFF (hh, ACTIVITY_LAG ( "_CEL_CS_ACTIVITIES"."EVENTTIME", 1),"_CEL_CS_ACTIVITIES"."EVENTTIME" ) - 

It has two issues.

 

  1. Due to Compound filter, the 4 activities are not showing up (which is the requirement), but the above PQL calculates the DateDiff for the 10 all Activities. So,When the 6 Activities showing up in the report, the DateDiff does not makes sense, that's because datediff is calcalated for all the 10 activity at the background. Any thoughts on how can I fix it?

 

2.Activity A happens at Jan 10th 8 O'clock.

Activity B happens at Jan 11th 10 O'clock.

DateDiff of Hour function gives only the value as 2. But logically it should be 26 hours , because of a day difference. How can I fix that?

 

Thanks in Advance!

    1 reply

    janpeter.van.d
    Level 12
    Forum|alt.badge.img+26

    Hi @sachin.agarw,

     

    1. This is expected behaviour for Window and PU-functions, where Activity Lag is somewhat part of (it is an extension of the normal LAG function). You can solve this, by adding FILTER_TO_NULL (celonis.com) to your query. Alternatively you could use SOURCE - TARGET (celonis.com) to calculate lead times between events.
    2. The Datediff function should return 26 if you look at the examples in the docs: DATEDIFF (celonis.com). Be sure to use DATEDIFF(hh, <START_DATE>, <END_DATE>) or HOURS_BETWEEN(<START_DATE>, <END_DATE>), and not round the dates to hours using the HOUR(<date>) function.

     

    Hopefully this helps!

    Community Code of ConductAccessibility statement