Count of IDs in max date bucket

[Shubham_D]

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

ID	Date bucket as Dimension
1	0-7 days
1	7-15 days
2	7-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

[Shubham_D]

hi @Kushal_Chawda ,
Thanks will give it a check 

[Shubham_D]

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

ID	Date	Rule
1	01-10-2020	Online Course
1	10-10-2020	Offline 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 @Kushal_Chawda has mentioned below if possible

[Shubham_D]

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.

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

-Shubham

[GaryGiles]

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