buckets based on transaction date & todays date

[surajap123]

Hi ,

I want to use a calculated dimension only in UI(pivot table)that group the transaction dates into the below buckets.

0-30 days,
31-60 days
61-90 days
91+ days

I have created the below if() to create the buckets, it is excluding records for 0-30 days, not sure why. Please help.

if(Date(TransactionDate,'DD/MM/YYYY')< Date(today()-91, 'DD/MM/YYYY'), '91+ days',
if(Date(TransactionDate,'DD/MM/YYYY')< Date(today()-61, 'DD/MM/YYYY'), '61-90 days',
if(Date(TransactionDate,'DD/MM/YYYY')< Date(today()-31, 'DD/MM/YYYY'), '31-60 days',
if(Date(TransactionDate,'DD/MM/YYYY')< Date(today(), 'DD/MM/YYYY'), '0-30 days',''))))

[srchagas]

i would recommend do this on script, and using Interval

if(Interval(Date(TransactionDate,'DD/MM/YYYY')-today(),'DD') <= 30, '0-30 days',
if(Interval(Date(TransactionDate,'DD/MM/YYYY')-today()) > 30
and Interval(Date(TransactionDate,'DD/MM/YYYY')-today(),'DD') <=60, '31-60 days',
if(Interval(Date(TransactionDate,'DD/MM/YYYY')-today(),'DD') > 60
and Interval(Date(TransactionDate,'DD/MM/YYYY')-today(),'DD') <=90, '61-90 days',
'91+ days'))))

[surajap123]

Thanks for your time and effort. Unfortunately, your expression only returned 0-30 days records and ignored all the other buckets.

[srchagas]

My bad, I just put the order wrong, should be Today() - TransactionDate, it's getting a negative value.

=if(Interval(today()-TransactionDate,'DD') <= 30, '0-30 days',
if(Interval(today()-TransactionDate,'DD') > 30
and Interval(today()-TransactionDate,'DD') <=60, '31-60 days',
if(Interval(today()-TransactionDate,'DD') > 60 and Interval(today()-TransactionDate,'DD') <=90, '61-90 days',
'91+ days')))

this one works 100%

[rwunderlich · Accepted solution]

Proper dates are stored as integers, so you shouldn't need the Date() function. It could be coded as 

if(Today() - TransactionDate >= 91, '91+ days',
if(Today() - TransactionDate >= 61, '61-90 days',
if(Today() - TransactionDate >= 31, '31-60 days',
if(TransactionDate < Today(), '0-30 days', 'Future'
))))

If it's not working as expected, make a table of Transactions with the Measure "Today()-TransactionDate" to investigate.

-Rob

[Ahidhar]

you can try this in script or create a master dimension using it

If(Outstandingdays<=30,dual('0-30 Days',1),
if(Outstandingdays>30 and Outstandingdays<=60,Dual('31-60 Days',2),
if(Outstandingdays>60 and Outstandingdays<=90,dual('61-90 Days',3),
if(Outstandingdays>90 ,dual('91+ Days',4))))) as daysbucket

where Outstandingdays is

Date(today(),'DD/MM/YYYY')-Date(TransactionDate,'DD/MM/YYYY')

[rwunderlich]

Date(today(),'DD/MM/YYYY')-Date(TransactionDate,'DD/MM/YYYY')

Why the Date() function? Can we not just use "today() - TransactionDate"?

-Rob