Hello all,
I am stuck with this problem I would like to share. The goal is to make a chart / line chart showing the monthly evolution of the Weighted Payment Days for a selection of customers.
Our Customer table has these fields:
| Customer |
|---|
| CustomerID |
| InvoiceID |
| ClosingDate - |
| PaymentDays (the number of days until the invoice was paid) |
| Amount |
The goal is a chart like this (it can later be converted to a line chart I suppose).
| Month | Weighted Payment Days |
|---|
| Apr 2016 | 65 |
| Mar 2016 | 67,6 |
| Feb 2016 | 66,7 |
| Jan 2016 | 70 |
| ... | ... |
I have tried this way:
- I have created a variable named vClosingDate.
- The calculation for the Weighted Payment Days is as follows:
=
sum({ < [ClosingDate] = {">=$(=date(AddMonths($(vClosingDate),-12),'DD/MM/YYYY')) <=$(=date($(vClosingDate),'DD/MM/YYYY'))"} > } [PaymentDays* Amount])
/
sum({ < [ClosingDate] = {">=$(=date(AddMonths($(vClosingDate),-12),'DD/MM/YYYY')) <=$(=date($(vClosingDate),'DD/MM/YYYY'))"}, > } [Amount])
It takes into account all those invoices closed 12 months back from the selected ClosingDate, and does the division of sum ([PaymentDays* Amount]) / sum [Amount]
I am using the vClosingDate as the dimension for the chart, but this way I get a correct result, but only for the date selected, something like this:
| monthname(vClosingDate) | Weighted Payment Days |
|---|
| Apr 2016 | 65 |
But no more rows are available using this approach. Is there any way I could get the rest of time periods?
Any help will be highly appreciated!
