Sum quantity within certain date range only

Dear all,

Currently we are doing some analysis on our O2C process. We are making use of a data model in which the delivery document line item is the central case_id in the case table. Attached to this case table are, amongst others, the earlier steps in the process (i.e. Sales Order data) as well as later steps in the process (i.e. Invoice data). A graphical representation for the analysis of 1 SO Line item (which contains 2 cases here) looks as follows:

In the above example, the specific SO_line 100 consists of two deliveries and two invoices (delivery line item:invoice = 1:1 relationship)

In Celonis we’ve created the following overview for this SO Line item:

INVOICE Q is defined as PU_SUM(“VBAP”, “_CEL_O2C_CASES”.“INVOICE_QTY”)

Besides, we have a date picker component to filter on billing date. However, so far it doesn’t work as we hoped it would. The idea is that if we enter 01/01/2019 - 31/12/2019 as date range, only the first billing document (Dec 20th) is taken into account. Hence, we would like to see 15,000 as INVOICE Q if we do so. However, whatever date range we provide, it keeps showing the total of 30,000.

If I create a separate single KPI component and simply use SUM("_CEL_O2C_CASES".“INVOICE_QTY”), it works as expected.

Now the question is how can we integrate this in the KPI which is in the table?

Thanks!
Jeroen

Dear Jeroen,

in regard to your date-picker question: I had a similar problem which I managed to solve by adding a PU component to a ROUND_MONTH Dimension. My formula looks like this:
ROUND_MONTH(PU_LAST("V_SD_SO_CASES", "V_SD_SO_ACTIVITES"."TIMESTAMP_WERT", "V_SD_SO_ACTIVITES"."STATUS_SHORT") = 1 AND "V_SD_SO_ACTIVITES"."STATUS_SHORT" = <%=Status_2%>))

where Status_2 is a variable specifying the status of which the last occurence should be counted if it occurs in a given month.

Maybe you can use something similar.

Best regards,
Rosemarie