topic Re: Compare a field to a specific Hour in QlikView

Compare a field to a specific Hour

Anonymous — Wed, 17 Sep 2014 20:24:15 GMT

Hello !

I have a Data field which have this format:

'DD-MM-YYYY hh:mm:ss'

I want to know the amount of time which is more than 6:30. Example:

17/09/2014 07:10 - Result : 1:10

17/09/2014 08:10 - Result : 2:10

I need the total (3h20m). How can i do this comparation?

if (

Date(Data,'DD-MM-YYYY hh:mm:ss') > Time('06:30', 'hh:mm'),

Sum ( Date(Data,'DD-MM-YYYY hh:mm:ss') - Date(Data,'DD-MM-YYYY 06:30') )

)

Something like this. How to do?

Thanks!

Morandi

Re: Compare a field to a specific Hour

maxgro — Wed, 17 Sep 2014 20:35:12 GMT

load * inline [

dt, result

17/09/2014 07:10, - Result : 1:10

17/09/2014 08:10, - Result : 2:10

];

expression is

interval(sum(frac(date#(dt, 'DD/MM/YYYY hh:mm')) - Time#('06:30', 'hh:mm')))

Re: Compare a field to a specific Hour

Anonymous — Wed, 17 Sep 2014 20:40:09 GMT

Thanks Massimo !

It works !

How can i compare only values bigger than 06:30, now?

Thanks!

Re: Compare a field to a specific Hour

maxgro — Wed, 17 Sep 2014 20:46:59 GMT

maybe

interval

(

sum ( if(frac(date#(dt, 'DD/MM/YYYY hh:mm')) > Time#('06:30', 'hh:mm'),

frac(date#(dt, 'DD/MM/YYYY hh:mm')) - Time#('06:30', 'hh:mm'))

)

Re: Compare a field to a specific Hour

Anonymous — Mon, 22 Sep 2014 13:31:15 GMT

Hello Massimo, it didnt worked.

I have this expression :

num( sum ({<evento={"Saida"}>}Quantidade))

Now i need to insert in set analysis the field "Data", and only sum everything if it is bigger than 6:30 (need to insert 6:30 in a variable).

I'm trying but its not working.

Any idea?

Thanks !

Morandi

Re: Re: Compare a field to a specific Hour

maxgro — Mon, 22 Sep 2014 21:45:00 GMT

see attachment

sum (if(Data>=floor(Data)+Time#('$(vt)', 'hh:mm') and evento='Saida',Quantidade))