topic Customer invoices aging throughout time in QlikView

Customer invoices aging throughout time

salto — Wed, 04 Dec 2013 12:12:46 GMT

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.

Re: Customer invoices aging throughout time

datanibbler — Wed, 04 Dec 2013 12:20:31 GMT

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

Re: Customer invoices aging throughout time

miikkaqlick — Wed, 04 Dec 2013 12:33:46 GMT

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

Re: Customer invoices aging throughout time

salto — Thu, 05 Dec 2013 11:20:50 GMT

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!