Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
nadkalyan
Partner - Creator II
Partner - Creator II

Calculate Number of Days Buckets based on Date difference (in days)

Hello,

I need to show a list that list Number of Days Buckets Range like '14 Days', '30 Days', '60 Days', '>60 Days'. I am using following Expression to create these Bucket Ranges-

=if((((aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate)) >= 0 and (aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate) <= 14),'14 Days',

if((((aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate)) >= 0 and (aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate) <= 30),'30 Days',

if((((aggr(max(LoadedDate), OpportunityNumber))-OpportunityCloseDate) >= 0 and (aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate)<= 60),'60 Days','>60 Days')))

But for some reason, the above expression is not seem to be working; when I select '14 days' , other days  greater than 14 are also showing up in the Data.

As you can see in below screenshot-

QV-days calc.jpg

Any assistance on how I can obtain desired results is highly appreciated.

Regards,

Kalyan

6 Replies
Not applicable

Hi, try the following:

=if((((aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate)) >= 0 and (aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate) <= 14),'14 Days',

if((((aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate)) >14

and (aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate) <= 30),'30 Days',

if((((aggr(max(LoadedDate), OpportunityNumber))-OpportunityCloseDate) >30

and (aggr(max(LoadedDate), OpportunityNumber)-OpportunityCloseDate)<= 60),'60 Days','>60 Days')))

Regards.

nadkalyan
Partner - Creator II
Partner - Creator II
Author

Thanks Quentin for quick reply.

But your condition specifies for '30 Days' to pick between 15 Days and 30 Days only, but thats not we intended for-

"14 Days" = Between 0 and 14

"30 Days"    = Between 0 and 30

"60 Days"= Between 0 and 60

">60 Days" = Greater than 60

Is there any logic (function) I can use to acheive as explained above.

Thanks Again,

Kalyan

Not applicable

Ok Nadkalyan. But in my understanding, it means that numbers between 0 and 14  are going to be marked as both '14 days' , '30 days' and '60 days' ?  I mean, because they all going to be less than 14, 30 and 60 at the same time

nadkalyan
Partner - Creator II
Partner - Creator II
Author

You are correct Quentin,  when User select '60 Days' or '30 Days' Option it should also pick up '14 Days' as 14 days is less than 30 or 60. Is there any way acheive this result?

Thanks
Kalyan

Not applicable

Can I please have a look at how your data look like (a qvw sample) ? It's quite an interresting task. We may find that it could better if you load the 'days buket'  in the back end with a function suchs as the Interval function?

nadkalyan
Partner - Creator II
Partner - Creator II
Author

Attached is sample qvw file with "Num of Days" and "Bucket" calculations. Please let me know if this is helpful!!

Appreciate your help Quentin all the way

Kalyan