# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for
Did you mean:
Not applicable

## how to divide the duration into intervals like 15 mins,1 Hr,24 Hr...

Hi All,

I am calculating the duration in minutes. means close date and enter date the duration is interval (close date- enter date, 'mm'). using this expression to calculate the duration in minutes.  divide the close date and enter date  into buckets like

15 minutes,

1 Hour,

24 Hour

> 24 Hours

temp:

[date-entered],

date_closed

if(Interval(date_closed-[date-entered],'mm')<= tmie#(15,'mm'),'<= 15minutes',

if(Interval(date_closed-[date-entered],'mm')> tmie#(15,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(60,'mm') ,'<= 1 Hr',

if(Interval(date_closed-[date-entered],'mm')> tmie#(60,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(1440,'mm') ,'<= 24 Hr',

if(Interval(date_closed-[date-entered],'mm')> tmie#(1440,'mm') , '> 24 Hr',)))) as category,

interval(date_closed - [date-entered]) as duration

FROM

(ooxml, embedded labels, table is Sheet3);

 <= 15 minutes <= 1Hr >  24 Hrs -

in that expression the Interval(date_closed-[date-entered],'mm') is divide into category.the data fetching only '<= 15 minutes', '<= 1 Hr', '> 24 Hr'  but not fetch the data ' <= 24 Hr' .the duration is divided into category is correct or not otherwise  any one plz tell me right way for that  solution

10 Replies
Contributor II

Your main problem is with the format of your [date entered] and [date closed] fields in excel. QV does not recognise these as dates therefore the QV interval function will not work.

Your data is in an inconsistent format, you need to clean it up first. If it is in excel (and this is not just a sample), then use find / replace to remove all the am and pm character. Use excel's value function to convert to a number and read teh result into QV. This will work for all the rows other than row 4 where the year is nonsense ie 20124.

Once the data has been cleaned, follow your other post on how to create a bucket.

MVP

temp:

If([date_closed]-[date-entered]<= 1/96,'<= 15minutes',

If([date_closed]-[date-entered]> 1/96 and [date_closed]-[date-entered]<=1/24,'<= 1 Hr',

If([date_closed]-[date-entered]> 1/24 and [date_closed]-[date-entered]<= 1,'<= 24 Hr',

If([date_closed]-[date-entered]> 1,'> 24 Hr',)))) as category,

Interval([date_closed] - [date-entered]) as duration;

Alt(Timestamp#([date-entered], 'DD/MM/YYYY hh:mm[:ssTT]'), Date#([date-entered])) as [date-entered],

Alt(Timestamp#([date_closed], 'DD/MM/YYYY hh:mm[:ss TT]'), Date#([date_closed])) as [date_closed]

FROM [http://community.qlik.com/servlet/JiveServlet/download/620572-129168/service.xlsx] (ooxml, embedded labels, table is Sheet3);

Not applicable
Author

Hi Marco,

small doubt regarding the above script. what is the value of 1/96=? ,1/24=?, 1 means plz explain me

MVP

see attachment

I added some rows to your excel and changed 1 value (year 20124)

RESULT

SCRIPT

```directory;

temp:
// 3 - calc bucket
*,
if(diffmm < 0, '<  0   ',
if(diffmm < 15, '< 15 mm',
if(diffmm < (60), '< 01 hh',
if(diffmm < (24*60), '< 24 hh',
'> 24 hh'
)))) as bucket
;
// 2 - difference in minutes
*,
dc -de as diff,
(dc-de)*24*60 as diffmm
;
// 1 - from excel to timestamp with alt function
[date-entered],
date_closed,
Timestamp(
alt(
date#([date-entered], 'DD/MM/YYYY hh:mm'),
date#([date-entered], 'DD/MM/YYYY'),
date#([date-entered], 'DD/MM/YYYY hh:mm:ssTT'),
), 'DD/MM/YYYY hh:mm:ss TT') as de,
Timestamp(
alt(
date#([date_closed], 'DD/MM/YYYY hh:mm'),
date#([date_closed], 'DD/MM/YYYY'),
date#([date_closed], 'DD/MM/YYYY hh:mm:ss TT'),
date#([date_closed], 'DD/MM/YYYY hh:mm:ssTT'),
), 'DD/MM/YYYY hh:mm:ss TT') as dc
//     if(Interval(date_closed-[date-entered],'mm')<= tmie#(15,'mm'),'<= 15minutes',
//     if(Interval(date_closed-[date-entered],'mm')> tmie#(15,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(60,'mm') ,'<= 1 Hr',
//     if(Interval(date_closed-[date-entered],'mm')> tmie#(60,'mm') and Interval(date_closed-[date-entered],'mm')<=tmie#(1440,'mm') ,'<= 24 Hr',
//     if(Interval(date_closed-[date-entered],'mm')> tmie#(1440,'mm') , '> 24 Hr',)))) as category,
//     interval(date_closed - [date-entered]) as duration

FROM
[service1.xlsx]
(ooxml, embedded labels, table is Sheet3);
```
MVP

1/24 = 1 day / 24 = 1 hour

1/96 = 1/4 hour = 15 min

Hope this helps

Regards

Marco

Not applicable
Author

Thank you Marco

MVP

You're welcome.

Thanks

Regards

Marco

Not applicable
Author

How to close the thread

MVP

Mark an answer of your choice correct (if you like others helpful as well).

Thanks

Regards

Marco

Community Browser