Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

How to Display Double Aging Bucket in Single Pivot table?

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

1 Solution

Accepted Solutions

Re: How to Display Double Aging Bucket in Single Pivot table?

Hi,

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

QlikCommunity_Thread_267920_Pic1.JPG

QlikCommunity_Thread_267920_Pic3.JPG

QlikCommunity_Thread_267920_Pic4.JPG

QlikCommunity_Thread_267920_Pic2.JPG

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

13 Replies
ziadm
Valued Contributor

Re: How to Display Double Aging Bucket in Single Pivot table?

please provide sample data

Partner
Partner

Re: How to Display Double Aging Bucket in Single Pivot table?

Here is the sample data

manas_bn
Contributor

Re: How to Display Double Aging Bucket in Single Pivot table?

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$);

range.PNG

Cheers!

Partner
Partner

Re: How to Display Double Aging Bucket in Single Pivot table?

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
Contributor

Re: How to Display Double Aging Bucket in Single Pivot table?

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.range pivot.PNG

Partner
Partner

Re: How to Display Double Aging Bucket in Single Pivot table?

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
Contributor

Re: How to Display Double Aging Bucket in Single Pivot table?

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. 

Re: How to Display Double Aging Bucket in Single Pivot table?

Hi,

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

QlikCommunity_Thread_267920_Pic1.JPG

QlikCommunity_Thread_267920_Pic3.JPG

QlikCommunity_Thread_267920_Pic4.JPG

QlikCommunity_Thread_267920_Pic2.JPG

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
Contributor

Re: How to Display Double Aging Bucket in Single Pivot table?

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.