topic Create groups by Range of Days in QlikView

Create groups by Range of Days

Fri, 11 May 2012 17:13:19 GMT

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

john_duffy — Fri, 11 May 2012 19:32:29 GMT

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

chematos — Fri, 11 May 2012 20:27:23 GMT

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.

Create groups by Range of Days

Fri, 11 May 2012 20:51:19 GMT

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

nilesh_gangurde — Sat, 12 May 2012 05:59:07 GMT

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

Jason_Michaelides — Sat, 12 May 2012 14:07:33 GMT

Instead of Interval(), try:

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

See attached.

Hope this helps,

Jason

Create groups by Range of Days

chematos — Mon, 14 May 2012 12:33:10 GMT

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

Re: Create groups by Range of Days

Mon, 14 May 2012 14:14:30 GMT

this is help for you.....