Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Shubham_D
Partner
Partner

Count of IDs in max date bucket

Hi All,

I have date buckets created on the basis of date as below for PIE chart,
0-7 days
7-15 days
15-30 days

IDDate bucket as Dimension
10-7 days
17-15 days
27-15 days


Now, because of some valid reason ID is getting counted in 2 buckets and requirement is to count ID in max date bucket only if falling under 2 date buckets.(As per data, I want to count ID in 7-15 days only)

Please help.

@sunny_talwar

Regards,
Shubham

Labels (3)
14 Replies
Shubham_D
Partner
Partner
Author

hi @Kush ,
Thanks will give it a check 

Shubham_D
Partner
Partner
Author

There is one more column due to which one ID can be in 2 different date buckets.
So as per today's date, we are checking expiry of IDs as per different RULES.
So because of different rules, ID falls in two date buckets but while taking count it should be irrespective of RULES and should get count in max date bucket.

0-7 Days    -  30 Sept - 6 Oct
7-15 Days  -  7 Oct - 14 Oct

IDDateRule
101-10-2020Online Course
110-10-2020Offline Course



sunny_talwar

@Shubham_D If you are able to make script changes, I would probably go with the route of creating a flag in the script, but if you can't then you will have to use Above() function to check the first appearance of the ID sorted by ID and then Date in the descending order when you do the count. To sort you can use Aggr's sorting... but all of this is a little complicated and I suggest using a flag from the script as @Kush has mentioned below if possible

Shubham_D
Partner
Partner
Author

Hi @sunny_talwar ,

Actually, I have already done same thing (as below snap) for some other requirements in the same table. So becoming difficult to implement suggested solution in existing left joins.
Thinking about creating these flags in DB itself.

Shubham_D_0-1601633661277.png

Thanks  @sunny_talwar  & @Kush 
Will try that suggestion if it doesn't work from DB flags.

-Shubham

GaryGiles
Specialist
Specialist

Try
=aggr(maxstring( Place Date Bucket as Dimension Logic Here ), ID)