10 Replies Latest reply: May 19, 2014 4:31 AM by Nagaian Krishnamoorthy

# group time intervals into time range

Hi, I want to group the time intervals into time range and this is what I scripted:

if(timediff>=30 and timediff <60,'<60',

if(timediff>=60 and timediff <90,'<90',

if(timediff>=90 and timediff <120,'<120',

if(timediff>=120 and timediff <150,'<150',

if(timediff>=150 and timediff <180,'<180',

if(timediff>=180 and timediff <210,'<210',

if(timediff>=210 and timediff <240,'<240',

if(timediff>=240 and timediff <270,'<270',

if(timediff>=270 and timediff <300,'<300',

if(timediff>=300 and timediff <330,'<330',

if(timediff>=330 and timediff <360,'<360')))))))))))) as timeinterval;

timediff contains the text type for the time intervals in minutes.

The result I got in the timediff listbox is only until '<90' when I'm supposed to get til '<360'. Thanks.

• ###### Re: group time intervals into time range

You may use the Class() function, e.g.

(timediff,30,'x') as [time range]

• ###### Re: group time intervals into time range

You may use qlikview inline load

min_value,max_value,Result

0,29,'<30'

30,59,'<60'

60,89,'<90'

90,119,'<120'

120,149,'<150'

150,179,'<180'

180,209,'<210'

210,239,'<240'

240,269,'<270'

270,299,'<300'

300,329,'<330'

330,359,'<360'

];

and can use Intervalmatch lookup function to find matches if you are loading from qlikview source like database table,Files or QVD.

• ###### Re: group time intervals into time range

Use Class functions as it is used to define intervals:

Class(timediff,30,'x')

• ###### Re: group time intervals into time range

Here u got the one u want

TimeDiff<=30,Dual('<30',1),

if(TimeDiff>30 and TimeDiff<=60,Dual('>30- <=60',2),

if(TimeDiff>60 and TimeDiff<=90,Dual('>60- <=90',3),

if(TimeDiff>90 and TimeDiff<=120,Dual('>90- <=120',4),

if(TimeDiff>120 and TimeDiff<=150,Dual('>120- <=150',5),

if(TimeDiff>150 and TimeDiff<=180,Dual('>150- <=180',6),

if(TimeDiff>180 and TimeDiff<=210,Dual('>180- <=210',7),

if(TimeDiff>210 and TimeDiff<=240,Dual('>210- <=240',8),

if(TimeDiff>240 and TimeDiff<=270,Dual('>240- <=270',9),

if(TimeDiff>270 and TimeDiff<=300,Dual('>270- <=300',10),

if(TimeDiff>300 and TimeDiff<=330,Dual('>300- <=330',11),

if(TimeDiff>330 and TimeDiff<=360,Dual('>330- <=360',12),

if(TimeDiff>360 ,Dual('>360',13)))))))))))))) as  TimeDiff1;

Amount

FROM

[Untitled 2.xls]

(biff, embedded labels, table is [Sheet1\$]);

• ###### Re: group time intervals into time range

Must my timediff be in num format? Now it's in text format.

• ###### Re: group time intervals into time range

hi rachel

when using dual function display will be in text format but sorting is done in ascending number format

while using in chart it will show in ascending format

for eg <=30

>30<=60

>60<=90

and so on

or else it will show like these

<=30

>60<=90

>30<=60

and so on

• ###### Re: group time intervals into time range

do u want like this

30

30-60

60-90

90-120

and so on