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

How to Display Double Aging Bucket in Single Pivot table?

shah_jigesh16 — Wed, 25 Nov 2020 16:16:04 GMT

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

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

ziadm — Mon, 17 Jul 2017 20:23:59 GMT

please provide sample data

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

shah_jigesh16 — Mon, 17 Jul 2017 21:03:22 GMT

Here is the sample data

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

manas_bn — Mon, 17 Jul 2017 21:57:24 GMT

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!

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

shah_jigesh16 — Tue, 18 Jul 2017 18:54:53 GMT

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

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

manas_bn — Tue, 18 Jul 2017 19:35:23 GMT

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.

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

shah_jigesh16 — Tue, 18 Jul 2017 19:52:36 GMT

Hi Manas,

Thank you for the prompt reply.

Yes this is what I was looking for.

Could you please share qvw for reference.

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

manas_bn — Tue, 18 Jul 2017 20:46:43 GMT

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?

MarcoWedel — Tue, 18 Jul 2017 21:57:07 GMT

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

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

manas_bn — Tue, 18 Jul 2017 22:16:33 GMT

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.

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

MarcoWedel — Tue, 18 Jul 2017 22:32:18 GMT

thanks,

actually this approach does not change the number of rows of the main table, instead the age days and buckets are loaded in a separate table.

This additional table and the %Key field add to the memory footprint, yes, but on the other hand age bucket values only have to be stored in a single field.

I guess it depends on the source data, which of both effects predominates.

maybe helpful:

Symbol Tables and Bit-Stuffed Pointers

regards

Marco

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

shah_jigesh16 — Wed, 19 Jul 2017 13:05:24 GMT

Hi Manas ,

Thank you for the efforts. But was looking more at dynamic Bucketing in script which Marco's solution will help getting that.

Have marked you solution as Helpful as we cannot mark 2 replies as Correct..

Thank you once again.

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

shah_jigesh16 — Wed, 19 Jul 2017 13:08:11 GMT

Hi Marco,

Thank you for the solution. Dint really think of using Crosstable using resident load.

Performance isn't an issue as data size is not huge.

It really helped a lot.

Thank you for the overall support.

Thanks and Regards,

Jigesh D Shah

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

manas_bn — Wed, 19 Jul 2017 16:35:21 GMT

Sure Jigesh. That was the better solution. I would have used it myself!