topic Re: How to round hour value in QlikView

How to round hour value

Sat, 09 Aug 2014 07:12:47 GMT

Round up HH:MM to HH

I am using the following expression in text object

= interval(avg({<A={'a'},Month={'Jul'},AvgAttended={'A'}>}HOUR),'hh:mm')

My requirement is round up hh:mm to hh like below:

If mm>=30 , hh value should be incremented by 1

if mm<30, hh value should be as it is

For example:

Thanks in Advance....

PrashantSangle — Sat, 09 Aug 2014 07:48:07 GMT

Hi,

use Time(),Second() or minute() or Hour() to achieve desired result.

In your case 56 is hr or min

Your solution is something like this,

if(Second(Time(time#('25:15','mm:ss'),'mm:ss'))>30,Minute(Time(time#('25:15','mm:ss'),'mm:ss'))+1,Minute(Time(time#('25:15','mm:ss'),'mm:ss')))

if(Second(Time(time#('56:37','mm:ss'),'mm:ss'))>30,Minute(Time(time#('56:37','mm:ss'),'mm:ss'))+1,Minute(Time(time#('56:37','mm:ss'),'mm:ss')))

Regards

maxgro — Sat, 09 Aug 2014 07:51:30 GMT

maybe using round to hour

=interval(round(interval#('56:37', 'hh:mm'), 1/24), 'hh')

=interval(round(interval#('25:15', 'hh:mm'), 1/24), 'hh')

MK_QSL — Sat, 09 Aug 2014 08:01:19 GMT

IF(LEFT(Interval(Time(Avg({<A={'a'},Month={'Jul'},AvgAttended={'A'}>}HOUR)),'hh:mm'),2)>=30,

LEFT(Interval(Time(Avg({<A={'a'},Month={'Jul'},AvgAttended={'A'}>}HOUR)),'hh'),2)+1,

Interval(Time(Avg({<A={'a'},Month={'Jul'},AvgAttended={'A'}>}HOUR)),'hh'))

MarcoWedel — Sat, 09 Aug 2014 09:24:14 GMT

= interval(round(avg({<A={'a'},Month={'Jul'},AvgAttended={'A'}>}HOUR),1/24),'hh')

Sat, 09 Aug 2014 09:28:51 GMT

Thanks for all your Reply...

Now I got the result.

MarcoWedel — Sat, 09 Aug 2014 09:33:37 GMT

= round(avg({<A={'a'},Month={'Jul'},AvgAttended={'A'}>}HOUR)*24)