Skip to main content

Hi,​

 I would highly appreciate your help as building given KPIs is business critical for our customer.

 

Many thanks in advance,

Michał

 

 

The issue is as follows:

Client required a KPIs of total time of approvals and number of invoices going through approval within wider AP process.

There may be different variants.

 

Case 1

- The CT is simply time between A1 and A8, timestamp of activity A3 (in between) does not affect the calculation

A1/ Invoice submitted for Approval

A3/ Change Price

A8/ Invoice Approved

 

 

Case 2

– in that case, the calculation should start with A1 and stop once invoice rejected (activity A2) and then restored from next submission (activity A5) until approved (activity A8). Time between A2 and A5 should not be included/ any activities in between should not be taken into account. So the CT here is A1 to A2 plus A5 to A8.

 

A1/ Invoice submitted for Approval

A2/ Invoice Rejected

A3/ Change Price

A5/ Invoice submitted for Approval

A8/ Invoice Approved

 

  

Case 3

– in that case, the calculation should stop once invoice rejected (activity A2) and restored from next submission (activity A5) until finally approved (activity A8). Note please,  the activity A6 (Invoice approved) means that there was a first approval, and A8 (Invoice approved - same name) means here it was a second (final) approval.

– Time between A2 and A5 should not be included/ any activities in between should not be taken into account.

 

A1/ Invoice submitted for Approval

A2/ Invoice Rejected

A5/ Invoice submitted for Approval

A6/ Invoice Approved

A8/ Invoice Approved

 

There are also other activities in the process, but they are not valid from this perspective.

 

I am trying to use Source/ Target function, but find it challenging to get the totals.

This will give you the total cycle time for each case. If you want the average between cases just wrap it in an average.
PU_SUM(
"CASE_TABLE",

CASE WHEN
(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice submitted for Approval'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice submitted for Approval'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END)

>


(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END)

OR

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END) IS NULL

OR

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END)

>

INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

THEN

DATEDIFF(ms,
"ACTIVITY_TABLE"."EVENTTIME",
LEAD(
"ACTIVITY_TABLE"."EVENTTIME",
ORDER BY ("ACTIVITY_TABLE"."EVENTTIME" ASC),
PARTITION BY ("ACTIVITY_TABLE"."CASE KEY")
))
ELSE 0
END

)
This will give you the total cycle time for each case. If you want the average between cases just wrap it in an average.
PU_SUM(
"CASE_TABLE",

CASE WHEN
(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice submitted for Approval'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice submitted for Approval'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END)

>


(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END)

OR

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END) IS NULL

OR

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
ELSE
LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'
THEN
INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )
END)
END)

>

INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,
ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),
PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

THEN

DATEDIFF(ms,
"ACTIVITY_TABLE"."EVENTTIME",
LEAD(
"ACTIVITY_TABLE"."EVENTTIME",
ORDER BY ("ACTIVITY_TABLE"."EVENTTIME" ASC),
PARTITION BY ("ACTIVITY_TABLE"."CASE KEY")
))
ELSE 0
END

)

HI Dylan,

thank you. I will check the results.

Hi @dylan.koury12  Unfortunately there formula does not count the time as expected. I checked for 3 different cases and it does not result with time between the required activities.

It looks like the challenge is with applying the right index order.

Attaching the excel sheet with analysis.

 

Appreciate your help.

Can you try this? I added some logic to skip activities that have no invoice submitted for approval before them and no invoice approved after them

 

CASE

WHEN

  LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice submitted for Approval'

      THEN

        INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

        ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

        PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

    END) IS NULL THEN 0

 

WHEN   LEAD(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Approved'

      THEN

        INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

        ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

        PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

    END) IS NULL AND "ACTIVITY_TABLE"."ACTIVITY (EN)" != 'Invoice Approved' THEN 0

 

WHEN

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice submitted for Approval'

  THEN

    INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

    ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

    PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )  

  ELSE

    LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice submitted for Approval'

      THEN

        INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

        ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

        PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

    END)

  END)

 

>

 

 

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'

  THEN

    INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

    ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

    PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

  ELSE

    LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'

      THEN

        INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

        ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

        PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

    END)

END)

 

OR

 

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'

  THEN

    INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

    ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

    PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

  ELSE

    LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'

      THEN

        INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

        ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

        PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

    END)

END) IS NULL

 

OR

 

(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'

  THEN

    INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

    ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

    PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

  ELSE

    LAG(CASE WHEN "ACTIVITY_TABLE"."ACTIVITY (EN)" = 'Invoice Rejected'

      THEN

        INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

        ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

        PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

    END)

END)

 

>

 

        INDEX_ORDER ( "ACTIVITY_TABLE"."ACTIVITY (EN)" ,

        ORDER BY ( "ACTIVITY_TABLE"."EVENTTIME" ASC ),

        PARTITION BY ( "ACTIVITY_TABLE"."CASE KEY" ) )

 

THEN

 

DATEDIFF(ms,

  "ACTIVITY_TABLE"."EVENTTIME",

  LEAD(

    "ACTIVITY_TABLE"."EVENTTIME",

    ORDER BY ("ACTIVITY_TABLE"."EVENTTIME" ASC),

    PARTITION BY ("ACTIVITY_TABLE"."CASE KEY")

  ))

ELSE 0

END

Reply


Terms & Conditions

ANY QUESTION?

Talk to an expert
FAQs
Contact us

MORE CELONIS

Academy arrow_outward
Documentation arrow_outward
Support Center arrow_outward
Partner Portal arrow_outward
Marketplace arrow_outward

CHECK US IN

LinkedIn
YouTube
Instagram
Facebook
Imprint
Disclaimer
Privacy Notice
Cookie Notice
Terms and Conditions
Celonis © 2024