Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
this is help for you.....
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.
Hi,
You could use the module function, with mod = 10 like this:
Load * INLINE[
[Open Date],
[Close Date],
round(mod(([Close Date]-[Open Date]),10) as Range
];
Hope this helps.
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
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
Instead of Interval(), try:
Floor((CloseDate-OpenDate)/10)*10 & ' to ' & (Floor((CloseDate-OpenDate)/10)*10+9) AS Range
See attached.
Hope this helps,
Jason
You are right sjcharles, mod is not necessary, with a simple division you could get the result.
Load * INLINE[
[Open Date],
[Close Date],
If([Close Date]-[Open Date]>0,round(([Close Date]-[Open Date])/10),0) as Range
];
Regards,
Chema
this is help for you.....