Create groups by Range of Days

What is the best way to create groups in script outside of nested IF() statements using the delta between open and closed dates?  Example:  if(close date - open date < 10 days then 0 to 9, if( close date - open date is between 10 and 20 then 10 to 20, etc.....  How would you use the interval() function for this?

• Re: Create groups by Range of Days

Hello.

You could use the following interval statement to get the number of days between two dates:

interval(floor([Date End]) - floor([Date Start]),'D') as [Days Between Dates]

Hope this helps.

John.

• Create groups by Range of Days

Hi,

You could use the module function, with mod = 10 like this:

[Open Date],

[Close Date],

round(mod(([Close Date]-[Open Date]),10) as Range

];

Hope this helps.

• Create groups by Range of Days

Don't think the Mod function will work as Open Date of 1/2/2012 and Close Date of 1/22/2012 (20 days difference) will be selected with the one like Open Date 1/2/2012 and Close Date of 1/12/2012 (10 days difference).

Stephen

• Create groups by Range of Days

You are right sjcharles, mod is not necessary, with a simple division you could get the result.

[Open Date],

[Close Date],

If([Close Date]-[Open Date]>0,round(([Close Date]-[Open Date])/10),0) as Range

];

Regards,

Chema

• Create groups by Range of Days

Hi Debb,

Use the below mention script at load time :

if(num(Date(CloseDateField)) - num(Date(OpenDateField)) < 10,'0 - 9',if(num(Date(CloseDateField)) - num(Date(OpenDateField)) >=10 and num(Date(CloseDateField)) - num(Date(OpenDateField)) <20 ,'10 - 20')) as Date_Range,

Then Use the Date_Range column to create the charts.

Regards,

Nilesh Gangurde

• Re: Create groups by Range of Days

Floor((CloseDate-OpenDate)/10)*10 & ' to ' & (Floor((CloseDate-OpenDate)/10)*10+9)    AS     Range

See attached.

Hope this helps,

Jason