topic Re: Creating Aging Buckets within QlikView in QlikView

Creating Aging Buckets within QlikView

Fri, 23 May 2014 16:41:55 GMT

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!

Re: Creating Aging Buckets within QlikView

eduardo_sommer — Fri, 23 May 2014 17:24:41 GMT

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.

Re: Creating Aging Buckets within QlikView

swuehl — Fri, 23 May 2014 17:45:20 GMT

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,

FROM ...;

Re: Creating Aging Buckets within QlikView

maxgro — Fri, 23 May 2014 17:54:15 GMT

some change to Re: Grouping Based on Value within Range

see attachment

Re: Creating Aging Buckets within QlikView

Fri, 23 May 2014 18:21:22 GMT

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

Re: Creating Aging Buckets within QlikView

maxgro — Fri, 23 May 2014 18:44:47 GMT

not sure to understand

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

Re: Creating Aging Buckets within QlikView

Fri, 23 May 2014 20:48:07 GMT

Thank you Massimo! The graph works perfectly!