PQL47 (PQL Function Library - CPM 4.7)

[1] The distinction whether an invoice was paid more than three days before the due date is made within the CASE WHEN statement by calculating the throughput time with the CALC_THROUGHPUT function. The CALC_THROUGHPUT operator takes the timestamp of the first occurrence of activity 'Clear Invoice' and the timestamp of the first occurrence of activity 'Due Date passed' and calculates the difference. The second parameter, given as REMAP_TIMESTAMPS operator, counts time units in the specified interval DAYS based on the timestamps in the activity table to enable the calculation of the throughput time. As the CALC_THROUGHPUT operator returns NULL if the end date is before the start date, the result of the calculation is wrapped in the COALESCE operator to return 0 in these cases. The result of the COALESCE operator is then compared to the specified three days. If the result is greater than 3, the CASE WHEN statement returns 1; otherwise 0.

The whole CASE WHEN statement is wrapped in the AVG operator, allowing to calculate the ratio of invoices paid more than three days early. By specifying the vendor name ("Invoice"."VendorName") as a dimension, the ratio is calculated per vendor. The two FILTER statements at the beginning of the query use PROCESS EQUALS to ensure that only cases with an already paid invoice and a specified due date are considered within the calculation.

For the example data, the query returns an early payment ratio of 0.5 for 'Vendor A'. Invoice '1' was not paid early because it was cleared three days before due date, while invoice '2' was paid early as it was cleared five days before due date. For 'Vendor B' the calculated early payment ratio is 1.0 because the only invoice related to this vendor, invoice '3', was paid early (four days before due date):

FILTER PROCESS EQUALS 'Clear Invoice';

FILTER PROCESS EQUALS 'Due Date passed';

"Invoice"."VendorName"

AVG (
    CASE
        WHEN
            COALESCE (
                CALC_THROUGHPUT (
                    FIRST_OCCURRENCE [ 'Clear Invoice' ]
                    TO
                    FIRST_OCCURRENCE [ 'Due Date passed' ] ,
                    REMAP_TIMESTAMPS ( "Activities"."Timestamp" , DAYS )
                ) ,
                0
            )
            >
            3
        THEN
            1
        ELSE
            0
    END
)