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
QlikWorld May 10-12, Online and Free! REGISTER NOW
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
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Shubham_D
Partner
Partner
Author

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 bucketCount(distinct ID)
0-7 Days0 or nothing
7-15 Days2


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

 



Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Kush
MVP
MVP

@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
Partner
Partner
Author

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

Shubham_D_1-1601391117541.png

 

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
Partner
Partner
Author

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

Kush
MVP
MVP

@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?