Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Creating Aging Buckets within QlikView

Hello,

I need help creating Aging Buckets. I think the best way to do that is in Load script. So far I was able to accomplish the following:

1) Calculated number of days using the following script:

Load Interval(Now()-[Estimated Delivery Date],'dd') as [Estimated Delivery Date Aging],

     Interval(Now()-[Actual Submit Date],'dd') as [Actual Submit Date Aging],

     Interval(Now()-[Reservation Created Date],'dd') as [Reservation Created Date Aging],

2) As a next step, I need to create buckets based on recently created 'Estimated Delivery Date Aging'. However, I get an error saying that 'Estimated Delivery Date Aging field doesn't exist'

3) I was able to create aging buckets within a graph. However, I'm not able to select individual aging category.

Here are the properties:

Ultimately, I need to be able to drill down to individual aging buckets (which I am not able to do at this point)

I appreciate your help!

1 Solution

Accepted Solutions
MVP
MVP

Re: Creating Aging Buckets within QlikView

6 Replies
Partner
Partner

Re: Creating Aging Buckets within QlikView

If your buckets were all of the same size, you could use the class function in your script. You can create a table with the boundaries of each bucket and use the intervalmatch function to associate the age with the buckets.

MVP
MVP

Re: Creating Aging Buckets within QlikView

Instead of creating multiple expressions, classify your aging values in the script, here is a discussion showing multiple approaches on a similar topic:

creating an age group from a date field

2) If you want to use a field you just created in your script, you need  a preceding load to reference this field:

LOAD *,

     if( A > 10,1,0) as Flag;

LOAD

     if( B = 'X', RAND()*20) as A,

     B

FROM ...;

MVP
MVP

Re: Creating Aging Buckets within QlikView

Not applicable

Re: Creating Aging Buckets within QlikView

Massimo,

Thank you so much for your response! I was able to recreate a graph. However, I'm not sure how I can make the buckets look like the original graph (assuming equal buckets)

I addition, the buckets should be assigned following this logic:

Current of Future - <=0

1-20 Days Overdue

21-40 Day Overdue

...

Over 100 days overdue

MVP
MVP

Re: Creating Aging Buckets within QlikView

not sure to understand

using my solution you can change the name and/or number and/or limit of buckets in the table RangeForIntervalMatch

Not applicable

Re: Creating Aging Buckets within QlikView

Thank you Massimo! The graph works perfectly!