Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been trying to calculate the difference between two dates and group the numeric output in different ways. I have found a way that works but is completely slow and complicated.
Ideally I would like to
(1) create a field called 'Days Since Due' by subtracting [Today()] - [Due_Date].
(2) group the output of the above formula by '0 to 30', '30 to 60', '60 to 90', and '90+'.
I think this is what I want but I couldn't get it to work:
if(num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<0,
if((num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<=-1
and num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))>=-30),'30',
if((num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<=-31
and num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))>=-60),'30 to 60',
if((num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))<=-61
and num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY'))>=-90),'60 to 90','+90')))) as OpenDaysPastDue,
The complexity should not lead to it's slowness... but in order to avoid the complexity... you can do a preceding load like this
LOAD *, If(Days_Since_today<0, If((Days_Since_today<=-1 and Days_Since_today>=-30),'30', If((Days_Since_today<=-31 and Days_Since_today>=-60),'30 to 60', If((Days_Since_today<=-61 and Days_Since_today>=-90),'60 to 90','+90')))) as OpenDaysPastDue;
LOAD Date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') as Date_Due_Converted_to_Date, Num(Date#([Date_Due],'MM/DD/YYYY')) as Date_Due_Converted_to_Number, Num(Today()) as Date_Today, Date#([Date_Due], 'MM/DD/YYYY') - Today() as Days_Since_today FROM ....;
But you can also do this
LOAD Date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') as Date_Due_Converted_to_Date, Num(Date#([Date_Due],'MM/DD/YYYY')) as Date_Due_Converted_to_Number, Num(Today()) as Date_Today, Date#([Date_Due], 'MM/DD/YYYY') - Today() as Days_Since_today, If((Date#([Date_Due], 'MM/DD/YYYY') - Today()) < 0, If(((Date#([Date_Due], 'MM/DD/YYYY') - Today()) >= -30), '30', If(((Date#([Date_Due], 'MM/DD/YYYY') - Today()) >= -60), '30 to 60', If(((Date#([Date_Due], 'MM/DD/YYYY') - Today()) >= -90), '60 to 90', '+90')))) as OpenDaysPastDue FROM ....;
I'm still kind of stuck on this one.
I am attaching a test file hoping it will help me describe my intent.
I would like to calculate the difference between two numbers. due_date - today()
And then take the product of that calculation and group it.
Between -1 and -30 = '30'
Between -31 and -60 = '60'
Between -61 and -90 = '60'
GT -90 = '90+'
Would you be able to share the Excel file - Test Data.xlsx? Also, can you provide the expected output from the data you will provide in the Excel
Attached is the excel file.
I would like the output of the field 'Days_Since_Today' to align with the output in field 'OpenDaysPastDue'.
'Days_Since_Today' is the calculated field. 'OpenDaysPastDue' is the grouped field. (30,60,90,90+)
Thanks for the assist :0)