Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can anyone suggest a simpler / more accurate logic to execute in the load statement?
The objective is to calculate the number between the due date and Today(). And than group that calculated number into a 'days delinquent bucket', Buckets are: 0 - 30 days past due date, 31 to 60 days past due date, 61 to 90 days past due date, and greater than 90 days.
This is the current code I have in the load statement. Most of the time it works but it's not 100%
if(IsNull(DueDate), 'n/a',
if(DateClosed>0,'n/a',
if(isNull(DateClosed) and DueDate=today(), 'n/a',
if(isNull(DateClosed) and DueDate>today(),'n/a',
if(IsNull(DateClosed) and DueDate>today()-29 and DueDate<today(), '30',
if(IsNull(DateClosed) and DueDate>today()-59 and DueDate<today()-29,'60',
if(IsNull(DateClosed) and DueDate>today()-89 and DueDate<today()-59,'90','90+'))))))) As OpenDaysPastDue,
Hi Erika,
Have you tried the Class() function?
Class(Today()-DueDate,30)
You can replace the 'x' which is created, something like
Replace(Class(Today()-DueDate,30),'<=x<',' to ')
It simplifies part of your script at least. Success.
I looked up Class function. I can see where it might work. I'm having a difficult time getting all of the variables accounted for in one string?
You can place the If()-statement you already created around the class(), I would think:
if(IsNull(DueDate) or DateClosed>0 or (isNull(DateClosed) and DueDate>=today()), 'n/a',
If(Today()-DueDate>90,'90+',
Replace(Class(Today()-DueDate,30),'<=x<',' to '))) As OpenDaysPastDue