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

[Anil_Babu_Samineni]

How is the data looks like for this? I can see, There are same for other two "7-15 Days"

[Shubham_D]

Hi,

So in this case, I don't want ID "1" to get considered in 0-7 Days 
I want ID "1" in 7-15 Days as its a max date bucket.
At the end, expected result is

Date bucket	Count(distinct ID)
0-7 Days	0 or nothing
7-15 Days	2

7-15 days will have count(distinct ID) = 2

[Anil_Babu_Samineni]

Sorry, I still not getting. Do you have sample data?

[Kushal_Chawda]

@Shubham_D  Probably you can change the bucket intervals. So 0-7 will be 0-6....7-15 will be 7-14 and so on

[Shubham_D]

Hi,
Because of valid logic , ID 1 is getting counted in both date bucket (0-7 Days, 7-15 Days), so in PIE chart ID count is getting repeated.
I want ID 1 to get counted in max date bucket which is 7-15 Days

Date Bucket:
0-7 Days - today() to (today()+7)
7-15 Days - (today()+8) to (today()+15)

eg

[sunny_talwar]

@Shubham_D How are you creating these buckets? In the script? What is the code you use to create the bucket? Can you change the code or do you want to fix this directly within the chart?

[Shubham_D]

Hi Sunny,

I am creating these buckets in script on the basis of date,

If(flag = 'F' and date >= date(today()) and  date<=date(today()+6) , '0-7 Days' ,
If(flag = 'F' and date >= date(today()+7) and  date<=date(today()+15) , '7-15 Days' ,
If(flag = 'F' and date > date(today()+15) and  date<=date(today()+30) , '15-30 Days' )))

There are some changes which I hv done for other requirements.
But we can change code if we get solution with backend or frontend changes.

-Shubham

[Kushal_Chawda]

@Shubham_D  try something like below

// This is your table where you have Bucket created
Data:
load  *
      Bucket
FROM Table;

// Create Bucket Number to identify the Max. Make sure that Bucket field name 
 and values in inline table should exactly match with Bucket created in Data
 
Left Join(Data)
LOAD * Inline [
Bucket,BucketNum
0-7 Days,1
7-15 Days,2
15-30 Days,3 ];

// load ID from table and count the bucket and assign max bucket
Final:
NoConcatenate
LOAD Distinct ID,
max(BucketNum) as Max_Bucket_Num,
Count(DISTINCT Bucket) as Bucket_count
Resident Data
Group by ID;

// create flag to tag each ID in max bucket
Left Join(Data)
LOAD Max_Bucket_Num as BucketNum,
ID,
1 as Max_Bucket_Flag
Resident Final;

DROP Table Final;

Now in your expression you can use flag like below

=count (distinct {<Max_Bucket_Flag={1}>}ID)

[sunny_talwar]

@Shubham_D So, this tells me that an ID should now fall under 2 buckets because if the date is <= Today() + 6, it cannot be >= Today() + 7. Right? I mean how can you ever have same ID and Date fall in the same bucket unless you have multiple dates for a single ID? Do you have multiple dates for single ID?