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

[nadkalyan]

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-

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

Regards,

Kalyan

[#2]

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]

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

[#4]

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]

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

[#6]

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]

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