Age Bucket - calculate on Selection - Qlik Community

Anonymous · ‎2016-05-11

Hi All,

I have a dataset as shown below

DefectID, Application, OpenDate, CloseDate, Status

001,App1,01/01/2016,01/01/2016,Closed

002,App2,01/01/2016,01/01/2016,Closed

003,App2,01/01/2016,02/01/2016,Closed

033,App4,01/01/2016,,Closed

004,App3,01/01/2016,04/01/2016,Closed

005,App3,01/01/2016,,Open

006,App3,02/01/2016,02/01/2016,Closed

007,App3,02/01/2016,02/01/2016,Closed

008,App4,02/01/2016,02/01/2016,Closed

009,App1,03/01/2016,03/01/2016,Closed

010,App1,03/01/2016,,Open

011,App3,04/01/2016,04/01/2016,Closed

012,App4,04/01/2016,04/01/2016,Closed

013,App3,04/01/2016,04/01/2016,Closed

014,App2,04/01/2016,05/01/2016,Closed

015,App2,05/01/2016,05/01/2016,Closed

016,App1,05/01/2016,05/01/2016,Closed

017,App1,05/01/2016,05/01/2016,Closed

018,App3,05/01/2016,05/01/2016,Closed

019,App4,05/01/2016,05/01/2016,Closed

020,App1,05/01/2016,,Open

I wanted to create a age bucket on selection of Date field.

Let say if i have a master calendar with following date

01/01/2016

02/01/2016

03/01/2016

04/01/2016

05/01/2016

Scenario 1:

when i select date 01/01/2016 from the master calendar it should pick those records that Opened on 01/01/2016 and exclude those records closed on 01/01/2016

so the filtered data and expression shown below

003,App2,01/01/2016,02/01/2016,Closed

033,App4,01/01/2016,,Closed

004,App3,01/01/2016,04/01/2016,Closed

005,App3,01/01/2016,,Open

Expression used

=Count({<DefectID = P({<OpenDate={'<=$(vSelectDt)'}>} DefectID)>} DefectID ) - Count({<DefectID = P({<CloseDate={'<=$(vSelectDt)'}>} DefectID)>} DefectID ) .

Now my question is i wanted to calculate the age for the above records and create a age bucket chart.

Expected O/P for Scenario 1

0- 1 Days | 4

Scenario 2:

when i select date 02/01/2016 from the master calendar it should pick those records that Opened on 01/01/2016 and 02/01/2016 and exclude those records closed on 01/01/2016 and 02/01/2016

so the filtered data and expression shown below

033,App4,01/01/2016,,Closed

004,App3,01/01/2016,04/01/2016,Closed

005,App3,01/01/2016,,Open

Same as above Expression used

=Count({<DefectID = P({<OpenDate={'<=$(vSelectDt)'}>} DefectID)>} DefectID ) - Count({<DefectID = P({<CloseDate={'<=$(vSelectDt)'}>} DefectID)>} DefectID ) .

Expected O/P for Scenario 2

0- 1 Days | 3

Scenario 3::

when i select date 03/01/2016 from the master calendar it should pick those records that Opened on 01/01/2016, 02/01/2016 and 03/01/216 exclude those records closed on 01/01/2016, 02/01/2016 and 03/01/216

so the filtered data and expression shown below

033,App4,01/01/2016,,Closed

004,App3,01/01/2016,04/01/2016,Closed

005,App3,01/01/2016,,Open

010,App1,03/01/2016,,Open

Same as above Expression used

=Count({<DefectID = P({<OpenDate={'<=$(vSelectDt)'}>} DefectID)>} DefectID ) - Count({<DefectID = P({<CloseDate={'<=$(vSelectDt)'}>} DefectID)>} DefectID ) .

Expected O/P for Scenario 3

0- 1 Days | 1

2-3 Days | 3

Soon on

Now my question is i wanted to calculate the age for the above records and create a age bucket chart dynamically.

anagharao · ‎2016-05-12

Hi Manoj,

Could you please elaborate on how the age bucket should be calculated? for example:

When date 01/01/2016 is selected the filtered data would be :

003,App2,01/01/2016,02/01/2016,Closed

033,App4,01/01/2016,,Closed

004,App3,01/01/2016,04/01/2016,Closed

005,App3,01/01/2016,,Open

the age of the rows are :

003,App2,01/01/2016,02/01/2016,Closed -- 1 Days

033,App4,01/01/2016,,Closed -- 0 Days

004,App3,01/01/2016,04/01/2016,Closed -- 3 days

005,App3,01/01/2016,,Open -- still open

So wouldnt the ouput be :

0-1 Days: 2

2-3 Days: 1

sunny_talwar · ‎2016-05-12

Would you be able to share the same file so that we don't have to recreate all these tables, master calendar and variables?

Anonymous · ‎2016-05-12

Hi Sunny,

Qvw file attached.

Anonymous · ‎2016-05-12

Hi Anagha, See attached file for explanation.

Anonymous · ‎2016-05-13

Any Help Plz...