topic Re: Create an aging table with cummulative values in QlikView

Create an aging table with cummulative values

elpida — Wed, 13 Apr 2022 13:42:06 GMT

Hello,

I want to create a table in which I have the dates of products purchases in rows and the number of days from date purchases in columns. As values, I have the payments that happened each date after the purchase.

For example:

The products that are purchased on 2021-12-01: We had 300 payments on the date 2021-12-02 (i.e 1 day after the purchase). Following on date 2021-12-03 (i.e 2 days after purchase), we had 400 payments, 300 from day 1 and 100 from day 2.

I have tried some calculations but they do not work.

Attached there is a sample of the format of the table, I want to reproduce.

Many thanks.

Re: Create an aging table with cummulative values

hic — Thu, 14 Apr 2022 07:54:38 GMT

Not knowing what your data looks like, I'll have to do some guesswork...

First, you should probably create a field "Delay" in the script, e.g.
Payment_Date - Purchase_Date as Delay

Then you can use this in a pivot table with
Dim1 (vertical dimension): Purchase_Date
Dim2 (horizontal dimension): Delay

If the number of payments is described by the expression Count(Payment), then you can create a measure called [Cumulative Payments] with the following definition:
RangeSum(Count(Payment), Before([Cumulative Payments]))

Re: Create an aging table with cummulative values

elpida — Thu, 14 Apr 2022 14:31:00 GMT

Hello @hic

Thank you for your feedback.

It seems to work, the only thing that is not correct is (see the table below):

the value of 7,115 on the 1st day belongs to 0day because the repayment happened the same day that the product had been purchased. Same for the rest of the values which have a shift of diff+1.

The calculations i Used for this pivot is:

product_purchase_Date: from raw data,

diff: Date(Date#(pay_date, 'YYYYMMDD'), 'YYYY-MM-DD') - Date(Date#(loan_date, 'YYYYMMDD'), 'YYYY-MM-DD')

Value: RangeSum(Before(sum(payments)))

Thank you.