Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Create groups by Range of Days

this is help  for you.....

7 Replies
john_duffy
Contributor III

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.

chematos
Valued Contributor II

Create groups by Range of Days

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

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

nilesh_gangurde
Valued Contributor

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

jason_michaelid
Honored Contributor II

Re: Create groups by Range of Days

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
Valued Contributor II

Create groups by Range of Days

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

Re: Create groups by Range of Days

this is help  for you.....

Community Browser