Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
salto
Specialist II
Specialist II

Customer invoices aging throughout time

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:

  • Entry Number
  • Posting Date
  • Amount
  • Initial Due Date

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.

3 Replies
datanibbler
Champion
Champion

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

miikkaqlick
Partner - Creator II
Partner - Creator II

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

salto
Specialist II
Specialist II
Author

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!