Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
=
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!
Would you be able to share a sample as it would be easy to test out rather than making random guesses
What is your vClosingDate Definition? Sample would be easier to give suggestion.
1st Guess : May be try Month([Closing Date]) as Dimension
Hello,
there goes a sample. Please let me know if you need any clarification.
Best regards.
I am not able to open this
Sorry, please check this one.