Calculate the difference between two numbers and Group the product

[leale1997]

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: 

date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') as Date_Due_Converted_to_Date,
     num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) as Date_Due_Converted_to_Number,
     num(date(today(),'DD/MM/YYYY')) as Date_Today,
     num(date(Date#([Date_Due],'MM/DD/YYYY'),'DD/MM/YYYY') ) - num(date(today(),'DD/MM/YYYY')) as Days_Since_today,

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,

I expanded it to this which works  but is slow  because it's so complex:

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,

[sunny_talwar]

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 ....;

[sunny_talwar]

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 ....;

[leale1997]

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+'

[sunny_talwar]

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

[leale1997]

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)