Hi @vincent.witte,

 

If I understand the question correctly, you want the first occurence of 'INPROG' after the last occurence of 'Queued'?

 

In that case, you were already very close. you need to use FIRST_OCCURRENCE E'INPROG'] instead of LAST_OCCURRENCE E'INPROG'].

 

AVG (

 CALC_THROUGHPUT (

  LAST_OCCURRENCE R 'QUEUED']

  TO

  FIRST_OCCURRENCE C'INPROG'],

  REMAP_TIMESTAMPS ( "eventlog"."timestamp" , MINUTES )

 )

)

 

Let me know if this is what you are looking for!

 

Kind regards,

Sverre Klein

 

 

Hi Sverre,

 

I assumed given your answer that it should take the first_occurence, automatically after a last occurence (to prevent going back in time), but I changed the formula (see screenshot), but it is not giving the expected 13 days * 24h * 60 minutes (the timestamp between 2023-12-20 10h45 and 2024-01-02 07h53

 

Can you reproduce? Should this work?

 

Hi @vincent.witte,

 

Ah apologies, I misread the question. Thanks for providing an example.

 

I tried to replicate it, and I got the following result that hopefully is also applicable to you.

 

 

The above table has a KPI that showcases the amount of hours between the last time Proposal Stage occurred and the next activity being Proposal Submitted.

 

This is the PQL statement I wrote. EDIT: There was a small mistake in the code, changed it now.

 

AVG(CASE WHEN PU_LAST(DOMAIN_TABLE("ACTIVITIES_TABLE"."EVENT_TIME"), "ACTIVITIES_TABLE"."ACTIVITY_NAME") = 'Proposal Stage'

 AND ACTIVITY_LEAD("ACTIVITIES_TABLE"."ACTIVITY_NAME", 1) = 'Proposal Submitted'

 THEN DATEDIFF(HH, "ACTIVITIES_TABLE"."EVENT_TIME", ACTIVITY_LEAD("ACTIVITIES_TABLE"."EVENT_TIME")) end)

 

The code is not perfect however, it assumes that the activity 'INPROG' will be directly after the last occurrence of 'QUEUED'. If it does not, it won't calculate the time difference.

 

I hope this helps you and/or puts you in the right track.

 

Kind regards,

Sverre Klein

Wow, I do learned a lot with this syntax/appraoch, but it is not yet fully working, because it doesn't compare the latest QUEUED=>INPROG, if there are more QUEUED=>INPROG combinations (rework), it takes the average. Count( ) shows 2, Max( ) shows 12.88, which is correct in this case, but fails if an earlier combination of QUEUED=>INPROG takes longer...

 

 

Somehow, we should only evaluate the latest QUEUED, and than use a formula like this.

If you have an earlier Proposal Stage and Proposal Submitted in the same case/ticket, it also takes the average.

 

Is there a work around?

Hi @vincent.witte,

 

Apologies for the late reply, I was on a small vacation 😎.

 

Hmm, I think I have a hunch why that happens, lets try a different approach. Try this instead. The code might not be the most efficient, but let's first see if we get the desired behavior. Instead of having a seperate case when in the beginning, lets put several CASE WHEN logic in the DATEDIFF statement.

 

AVG (

  DATEDIFF (

    HH ,

    CASE

      WHEN

        PU_LAST (

          DOMAIN_TABLE ( "ACTIVITIES_TABLE_HUBSPOT"."EVENT_TIME" ) ,

          "ACTIVITIES_TABLE_HUBSPOT"."ACTIVITY_NAME"

        )

        =

        'Proposal Stage'

      THEN

        "ACTIVITIES_TABLE_HUBSPOT"."EVENT_TIME"

    end ,

    CASE

      WHEN

        PU_LAST (

          DOMAIN_TABLE ( "ACTIVITIES_TABLE_HUBSPOT"."EVENT_TIME" ) ,

          "ACTIVITIES_TABLE_HUBSPOT"."ACTIVITY_NAME"

        )

        =

        'Proposal Stage'

        AND

        ACTIVITY_LEAD ( "ACTIVITIES_TABLE_HUBSPOT"."ACTIVITY_NAME" , 1 ) = 'Proposal Submitted'

      THEN

        ACTIVITY_LEAD ( "ACTIVITIES_TABLE_HUBSPOT"."EVENT_TIME" , 1 )

   end ))

 

What this code this is essentially the same. The first CASE WHEN statement gets event_time of the very last 'QUEUED'. The second CASE WHEN statement retrieves the event_time of the first occurrence of 'INRPOG' after the last occurrence of 'QUEUED'.

 

The two CASE WHEN statements allow for more nuanced query control, so hopefully this gets your desired outcome!

 

Let me know if you need more help. 😊

Hi @vincent.witte,

 

You can mimic your 'NEXT()' function as follows:

• Map all values that you want to exclude to NULL using Case When
• Use LEAD to search for next non-null value to return timestamp

Then you can apply the other operations:

• Optionally: apply MINUTES_BETWEEN to get the minutes (or HOURS_BETWEEN etc. or DATEDIFF) between two timestamps.
• Adjust formula to only apply this logic for the last 'QUEUED' Event

 

-- 1) Map timestamps to NULL if not 'INPROG'

CASE WHEN "eventlog"."activity" = 'INPROG' THEN "eventlog"."timestamp" ELSE NULL END

 

-- 2) Show timestamp of next 'INPROG'.

 LEAD(CASE WHEN "eventlog"."activity" = 'INPROG' THEN "eventlog"."timestamp" ELSE NULL END,

ORDER BY "eventlog"."timestamp" ASC,

PARTITION BY "eventlog"."case_id"

)

 

-- 3) Show time between current event and timestamp of next 'INPROG'.

MINUTES_BETWEEN("eventlog"."timestamp",

 LEAD(CASE WHEN "eventlog"."activity" = 'INPROG' THEN "eventlog"."timestamp" ELSE NULL END, ORDER BY "eventlog"."timestamp" ASC, PARTITION BY "eventlog"."case_id" )

)

 

-- 4) Only apply for latest 'QUEUED'

CASE

-- Only consider QUEUED

WHEN "eventlog"."activity" = 'QUEUED'

-- Only consider the event where the index is the same as the maximum index (= rework count)

AND INDEX_ACTIVITY_TYPE("eventlog"."activity") = CALC_REWORK ( "eventlog"."activity" = 'QUEUED' )

THEN << upper formula here >>

ELSE NULL

END

 

I hope this helps you!

 

Best regards,

Jan-peter

 

Hi @vincent.witte,

 

I hope @janpeter.van.d's answer or mine helped you out? Let us know!

 

 

I had a break (not a breakdown), hence the late response.

The suggestion from @janpeter.van.d worked. I had to tweak a bit ORDER BY needs ( ) as well as partition in my version of studio, and I had to take the max to prevent duplicates.

Currently working in Days, for checks to the 12.88 days between, but in minutes also ok.

 

Thanks! Also @sverre.klein11 

 

/*

 2024-04-02 v01 - 5 steps formula from inside out

 

*/

 

-- 5. add MAX to filter out the null resulting in duplicate rows

MAX

(

  CASE

  -- 4. Only apply when QUEUED AND it is the latest

  WHEN "eventlog"."status" = 'QUEUED'

  -- Only consider the event where the index is the same as the maximum index (= rework count)

  AND INDEX_ACTIVITY_TYPE("eventlog"."status") = CALC_REWORK ( "eventlog"."status" = 'QUEUED' )

  THEN

  -- 3 Show time between current event and timestamp of next 'INPROG'.  note: ORDER BY ( ) needs brackets

  DAYS_BETWEEN("eventlog"."timestamp",

      -- 2) Show timestamp of next 'INPROG'

      LEAD(

          -- 1) Map timestamps to NULL if not 'INPROG'  

          CASE WHEN "eventlog"."status" = 'INPROG' THEN "eventlog"."timestamp" ELSE NULL END,

      ORDER BY ( "eventlog"."timestamp" ASC),

      PARTITION BY ("eventlog"."ticketid")

      )

  )

  ELSE NULL

  END

)