Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
aaryanqvdevelop
Creator
Creator

Age Bucket - calculate on Selection

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.

5 Replies
anagharao
Creator II
Creator II

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

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

aaryanqvdevelop
Creator
Creator
Author

Hi Sunny,

Qvw file attached.

aaryanqvdevelop
Creator
Creator
Author

Hi Anagha, See attached file for explanation.

aaryanqvdevelop
Creator
Creator
Author

Any Help Plz...