Description
This example shows how to calculate the total number of days spent between creating and clearing multiple invoices of one purchase order.
In the Purchase-to-Pay process (P2P), there can be multiple invoices per case (that is, per Purchase Order Item). One example would be the payment by installments of one item. The 'Record Invoice Receipt' activity marks the receipt of an invoice, and 'Clear Invoice' marks the payment of the invoice.
Challenge
For each case, how do you calculate the total number of days spent between matching pairs of 'Record Invoice Receipt' and 'Clear Invoice' activities? The invoice number to which those activities belong is given as a separate column in the activity table.
Solution
In PQL, this question can be answered as follows:
Column1
PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."CASE" )
Column2
SUM ( DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) ) )
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
Step-by-Step Solution
Let's start developing the solution by solving a more simpler question: How can we calculate the number of days between the first 'Record Invoice Receipt' and the last 'Clear Invoice' activities of each case (not taking the invoice number into account)? The timestamps of those activities can be retrieved by using PU_FIRST and PU_LAST, respectively. The number of days between the resulting timestamps can be calculated using the DAYS_BETWEEN functionality.
Column1
"Cases"."CASE"
Column2
DAYS_BETWEEN ( PU_FIRST ( "Cases", "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( "Cases", "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) )
Activities
CASE : INT | ACTIVITY : STRING | EVENTTIME : DATE |
---|---|---|
1 | 'X' | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | Thu Jan 24 2019 13:00:00.000 |
Cases
CASE : INT |
---|
1 |
2 |
Foreign Keys
Activities.CASE | Cases.CASE |
When adding columns from the activity table to the query and thereby joining our previous result to the activity table, the DAYS_BETWEEN result of a case appears next to every activity of that case.
Column1
"Activities"."CASE"
Column2
"Activities"."ACTIVITY"
Column3
"Activities"."INVOICE_NUMBER"
Column4
DAYS_BETWEEN ( PU_FIRST ( "Cases", "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( "Cases", "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) )
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
Cases
CASE : INT |
---|
1 |
2 |
Foreign Keys
Activities.CASE | Cases.CASE |
To get the times between every pair of 'Record Invoice Receipt' and 'Clear Invoice' activities with the same invoice number, it is not enough to calculate the PU aggregations only for each case. Instead, we need to calculate them for each unique pair of CASE and INVOICE_NUMBER values. This can be achieved by creating a temporary child table with DOMAIN_TABLE, which contains those unique pairs of CASE and INVOICE_NUMBER values. The PU functions then return the according values for each of those pairs.
Column1
"Activities"."CASE"
Column2
"Activities"."ACTIVITY"
Column3
"Activities"."INVOICE_NUMBER"
Column4
DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) )
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
The goal is to eventually sum up all the values calculated by the DAYS_BETWEEN function per case. However, the number of days between each pair of 'Record Invoice Receipt' and 'Clear Invoice' activities always appears multiple times in the column: It appears next to every row in the activity table with the corresponding case and invoice number due to the join with the activity table. In order to only have it once per activity pair, we can set every time difference value except for those of the 'Record Invoice Receipt' activity to 0 (or NULL alternatively) by using a CASE WHEN statement.
Column1
"Activities"."CASE"
Column2
"Activities"."ACTIVITY"
Column3
"Activities"."INVOICE_NUMBER"
Column4
CASE WHEN "Activities"."ACTIVITY" = 'Record Invoice Receipt' THEN DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) ) ELSE 0.0 END
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
Now we can calculate the total number of days spent between 'Record Invoice Receipt' and 'Clear Invoice' activities for each case by summing up the values of the column we created per case.
Column1
"Activities"."CASE"
Column2
SUM ( CASE WHEN "Activities"."ACTIVITY" = 'Record Invoice Receipt' THEN DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) ) ELSE 0.0 END )
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
Since we want to see the Case ID in the result table, we had to join our throughput times to the activity table. That means we had to use a CASE WHEN in order to only sum up each value once. However, we could also avoid the join to the activity table by retrieving the Case ID in a PU function, using the same DOMAIN_TABLE as in the PU functions in the DAYS_BETWEEN statement. In the DOMAIN_TABLE, all distinct groups of case and invoice numbers are contained. Let's look at this without summing up all throughput times per case first. To get context for the numbers, we also include the invoice number using the same technique.
Column1
PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."CASE" )
Column2
PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."INVOICE_NUMBER" )
Column3
DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) )
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
Now we can sum up the throughput time values to get the final result. As we want to sum them up for each case, we need to remove the invoice number dimension again.
Column1
PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."CASE" )
Column2
SUM ( DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) ) )
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
As a bonus, we can also calculate the total number of days spent between 'Record Invoice Receipt' and 'Clear Invoice' activities over all cases. The CASE WHEN statement is not required here because the result is not joined to the activity or case table.
Column1
SUM ( DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", "Activities"."INVOICE_NUMBER" ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) ) )
Activities
CASE : INT | ACTIVITY : STRING | INVOICE_NUMBER : INT | EVENTTIME : DATE |
---|---|---|---|
1 | 'X' | null | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 100 | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 200 | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | 300 | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | 300 | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | 100 | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | 100 | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | 100 | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | 100 | Thu Jan 24 2019 13:00:00.000 |
Result
Column1 : FLOAT |
---|
13.0 |
If no INVOICE_NUMBER column is given in the activity table to group corresponding activities, we could assume that the 'Clear Invoice' activities appear in the same order as the corresponding 'Record Invoice Receipt' activities. So for each case, we could calculate the number of days between the first 'Record Invoice Receipt' and the first 'Clear Invoice', between the second 'Record Invoice Receipt' and the second 'Clear Invoice', and so on using the INDEX_ORDER functionality:
Column1
"Activities"."CASE"
Column2
SUM ( CASE WHEN "Activities"."ACTIVITY" = 'Record Invoice Receipt' THEN DAYS_BETWEEN ( PU_FIRST ( DOMAIN_TABLE ( "Activities"."CASE", INDEX_ORDER ( "Activities"."ACTIVITY", GROUP ( "Activities"."CASE", "Activities"."ACTIVITY" ) ) ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Record Invoice Receipt' ), PU_LAST ( DOMAIN_TABLE ( "Activities"."CASE", INDEX_ORDER ( "Activities"."ACTIVITY", GROUP ( "Activities"."CASE", "Activities"."ACTIVITY" ) ) ), "Activities"."EVENTTIME", "Activities"."ACTIVITY" = 'Clear Invoice' ) ) ELSE 0.0 END )
Activities
CASE : INT | ACTIVITY : STRING | EVENTTIME : DATE |
---|---|---|
1 | 'X' | Tue Jan 01 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | Wed Jan 02 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | Thu Jan 03 2019 13:00:00.000 |
1 | 'Record Invoice Receipt' | Fri Jan 04 2019 13:00:00.000 |
1 | 'Clear Invoice' | Sun Jan 06 2019 13:00:00.000 |
1 | 'Clear Invoice' | Thu Jan 10 2019 13:00:00.000 |
2 | 'Record Invoice Receipt' | Mon Jan 21 2019 13:00:00.000 |
2 | 'Y' | Tue Jan 22 2019 13:00:00.000 |
2 | 'Clear Invoice' | Thu Jan 24 2019 13:00:00.000 |