Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

1 Solution

Accepted Solutions
Not applicable
Author

this is help  for you.....

View solution in original post

7 Replies
john_duffy
Partner - Creator III
Partner - Creator III

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.

chematos
Specialist II
Specialist II

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.

Not applicable
Author

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

nilesh_gangurde
Partner - Specialist
Partner - Specialist

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Instead of Interval(), try:

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

See attached.

Hope this helps,

Jason

chematos
Specialist II
Specialist II

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

Not applicable
Author

this is help  for you.....