Show amount of dunned invoices in observation period

[chris186]

Hi,

I have sample data like this:

doc	doc_row	dunning_date	customer	dunning_stage	amount
084017749192	1	13.09.2018	XYZ	1	4,00
674003496890	2	13.09.2018	XYZ	1	3427,11
082001799023	1	27.09.2018	XYZ	1	1,76
084017749192	1	27.09.2018	XYZ	3	4,00
674003496890	2	27.09.2018	XYZ	3	3427,11
082001799023	1	05.11.2018	XYZ	3	1,76
084017749192	1	05.11.2018	XYZ	4	4,00
674003496890	2	05.11.2018	XYZ	4	3427,11

These are dunned invoices for a customer. One position (doc and doc_row)  can occur multiple times because it was dunned multiple times. When a position is dunned one more time the dunning_stage increases.

Now I want to display the amount of dunned invoices in an observation period which is selected by the dunning_date. Lets say it is quarter 4 in 2018.

It is important that only the invoice-positions in the highest dunning_stage are shown/calculated. The dunning_date in the result table has to be a dynamic dimension so you can switch between the date itself and year-month, year-quarter and so on.

The desired output should be something like this for q4 2018:

date	customer	amount
05.11.2018	XYZ	3432,87

I tried the aggregate function like 

sum(aggr(max(amount),doc,doc_row)) 

but I am not able to include the dunning_stage. Does anybody know how to solve that problem?

Best regards, Chris

[sunny_talwar]

May be try this

Sum(Aggr(
FirstSortedValue(amount, -dunning_stage)
, customer, doc))

[chris186]

Hmm, that doesn't change anything...

[sunny_talwar]

Would you be able to share a sample to show the issue?

[chris186]

Sorry for my late reply, here is a sample.

So the Output should always be the docs with the highest dunning_stage.

Thanks and best regards

[sunny_talwar]

Try this

=Sum(Aggr(
If(DUNNING_STAGE = Max(TOTAL <CUSTOMER, $(='[' & GetCurrentField(Laufdatum) & ']')> DUNNING_STAGE), Sum(AMOUNT))
, CUSTOMER, $(='[' & GetCurrentField(Laufdatum) & ']'), DOC))

[chris186]

Hi,

thanks for you help but maybe I wasn't clear enough. 

The output should be the highest DUNNING_STAGE per DOC/ROW at a given time. So lets say quarter 4 in 2018 is selected.

Here is what should be in the output (single rows):

DOC 082001799023 with DUNNING_STAGE 3 1,76 (3 is the highest DUNNING_STAGE of DOC 082001799023/ ROW 1 in quarter 4 2018)
DOC 084017749192 with DUNNING_STAGE 4 4,00 (4 is the highest DUNNING_STAGE of DOC 084017749192/ ROW 1 in quarter 4 2018)
DOC 674003496890 with DUNNING_STAGE 4 3427,11 (4 is the highest DUNNING_STAGE of DOC 674003496890/ ROW 2 in quarter 4 2018)

If you select quarter 3 in 2018 the output should be (single rows):

DOC 082001799023 with DUNNING_STAGE 1 1,76 (1 is the highest DUNNING_STAGE of DOC 082001799023/ ROW 1 in quarter 3 2018)
DOC 084017749192 with DUNNING_STAGE 3 4,00 (3 is the highest DUNNING_STAGE of DOC 084017749192/ ROW 1 in quarter 3 2018)
DOC 674003496890 with DUNNING_STAGE 3 3427,11 (3 is the highest DUNNING_STAGE of DOC 674003496890/ ROW 2 in quarter 3 2018)

Hope you understand what I mean.

Thanks a lot, Chris

[sunny_talwar]

This?