How to Display Double Aging Bucket in Single Pivot table?

[shah_jigesh16]

Hi All,

We have a requirement where we would like to display 2 aging buckets in single Pivot table in Qlikview.

For Example: Create below 2 fields for each record in the table.

Transfer Bucket = [Transfer Date] - [Start Date] and create a bucket field "Transfer_Aging".

Active Bucket = [Active Date] - [Start Date] and create a bucket field "Active_Aging".

Bucket creation was done as expected. But the issues we are facing is on how to display above 2 bucket fields in same Pivot table.

Please find attached image on the expected output.

Please help on achieving the results as shown in attachment.

Thanks and regards,

Jigesh

[ziadm]

please provide sample data

[shah_jigesh16]

Here is the sample data

[manas_bn]

Hi Jigesh,

Two ways to do this. One is through preceding load in the backend script. Second is through set analysis in the expression. Attached is the sample for Active buckets using both methods, you can do the same for transfer buckets.

THROUGH SET ANALYSIS:

Count({<[Active Days] = {'>=1<=100'} >} [Primary Key])

THROUGH SCRIPT:

// Preceding Load

LOAD  *,

// Create buckets 0, 1-100 and 101-500

if( Active=0, 1,

      if( Active >=1 and Active <=100, 2,

      if( Active >100 and Active <=500, 3,4))) as [Active Range];

load [Primary Key],

    Status,

    [Start Date],

    [Transfer Date],

    [Active Date],

    [Active Date]-[Start Date] as Active Days,

    [Transfer Date]-[Start Date] as Transfer Days

FROM

(biff, embedded labels, table is Sheet1$);

Cheers!

[shah_jigesh16]

Hi Manas,

Thank you for the answer and explanation.

Query here is more on the UI front.

That is as you have mentioned 2 created 2 bucket field in Script that is already done.

How do we display both bucket fields in UI in same pivot table without hardcoding.

Thank you once again.

Please help

[manas_bn]

Something like this? I just created a pivot table with Status as Dimension and count(Primary Key) for each bucket as the expression. The labels are actually text boxes for Active and Transfer Aging. Not sure if this is exactly what you are looking for though.

[shah_jigesh16]

Hi Manas,

Thank you for the prompt reply.

Yes this is what I was looking for.

Could you please share qvw for reference.

[manas_bn]

Here you go. Alternatively, you could also change the background color on the expressions so it's easy to differentiate between Active/Transfer.

You can explore other options for labels as well. 

[MarcoWedel · Accepted solution]

Hi,

another solution could be to create a generic age bucket field and an age type e.g. like this:

table1:
LOAD AutoNumberHash128([Primary Key],Status) as %Key, *
FROM [https://community.qlik.com/servlet/JiveServlet/download/1308467-287720/Double%20Aging%20Bucket.xls] (biff, embedded labels, table is Sheet1$);

tabAging:
CrossTable (AgeType, AgeDays)
LOAD %Key,
      [Transfer Date]-[Start Date] as Transfer,
      [Active Date]-[Start Date] as Active,
      [Active Date]-[Transfer Date] as TransferActive
Resident table1;
Left Join
LOAD Distinct
     AgeDays,     
     If( AgeDays< 50,Dual( '<50 days'      ,  1),
      If(AgeDays<100,Dual('>=50 <100 days' , 50),
      If(AgeDays<200,Dual('>=100 <200 days',100),
      If(AgeDays<500,Dual('>=200 <500 days',200))))) as Aging
Resident tabAging;

hope this helps

regards

Marco

[manas_bn]

This is an excellent solution! Thank you!

Would this have any performance impact since we are doubling the number of records? Just trying to learn.