Creating logic to group delinquent records by bucket or # of days past due?

[leale1997]

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,

[Janneke]

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.

[leale1997]

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?

[Janneke]

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