Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be try this
Sum(Aggr( FirstSortedValue(amount, -dunning_stage) , customer, doc))
Hmm, that doesn't change anything...
Would you be able to share a sample to show the issue?
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
Try this
=Sum(Aggr(
If(DUNNING_STAGE = Max(TOTAL <CUSTOMER, $(='[' & GetCurrentField(Laufdatum) & ']')> DUNNING_STAGE), Sum(AMOUNT))
, CUSTOMER, $(='[' & GetCurrentField(Laufdatum) & ']'), DOC))
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
This?