Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am facing a design problem. I built a chart that showed the aging of the customer invoices, on a certain Aging Date.
I built my table reading from the Customer Ledger Entry table, on which there are three fields:
I calculate the aging in an already selected certain date (AgingDate) with this expression (I will write it in pseudo-code):
Amount Not overdue
= sum ({<InitialDueDate > AgingDate , PostingDate <= AgingDate>}, Amount) //Posted but not overdue Amount
Overdue 0-30 days
= sum ({<InitialDueDate <= AgingDate, InitialDueDate > AgingDate-30, PostingDate <= AgingDate>}, Amount) //Posted and overdue less than 30 days Amount
And so on for the Overdue 31-60 days, 61-90 days, and more than 90 days.
This works for a chart and with a single AgingDate posted.
BUT! (because there is always a BUT!): wI would like to see the evolution in time of this amounts?
How should I change my data model (or my formula) so I could obtain the same results in a chart, using a time dimension?
Since my formula is based in a fixed date, I do not see the way to link it to a calendar.
Any suggestion is welcome.
Hi SALTO,
you're right - like this, you are calculating it for one date only, so you cannot link it to your calendar.
I would suggest you try to calculate this in your script - the set_expressions don't look too complicated, so I guess you could do it - then you have two fixed fields for those two values and you can use a time_dimension.
HTH
Best regards,
DataNibbler
Hi!
I assume that AgingDate = Today().
Then you can change AgingDate = Max(Date)
Then you can choose one or multiple dates from calendar and you will get results.
If you want selection to be month-based (just select month, not date) then add to set analysis Month =
I don't see why couldn't link that to calendar.
Br,
Miikka
Hi,
thanks Mikka and DataNibble, for your answers.
Your suggestion permits the calculation of the total balance, which is:
= sum ({<PostingDate <= Max(DateID)>}, Amount) //All Amounts (overdue or not) Correct!!!!
But when it comes to the not overdue amount, or overdue <30 days, I need to add the Initial Due Date to the formula:
Amount Not overdue
= sum ({<InitialDueDate > Max(DateID), PostingDate <= Max(DateID)>}, Amount) //Posted but not overdue- fails
Amount Overdue < 30 days
= sum ({<InitialDueDate > Max(DateID), PostingDate <= Max(DateID)>}, Amount) //Posted overdue < 30 days - fails
I think the error comes from the comparison InitialDueDate > Max(DateID), but, which value should I take? I have tried
InitialDueDate > (DateID), InitialDueDate > Only(DateID) ... with no results.
Any help on this will be appreciated!