Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Anonymous
Not applicable

Compare a field to a specific Hour

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

5 Replies
MVP
MVP

Re: Compare a field to a specific Hour

o:

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')))

1.png

Anonymous
Not applicable

Re: Compare a field to a specific Hour

Thanks Massimo !

It works !

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

Thanks!

MVP
MVP

Re: Compare a field to a specific Hour

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'))

  )

  )

Anonymous
Not applicable

Re: Compare a field to a specific Hour

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

MVP
MVP

Re: Re: Compare a field to a specific Hour

see attachment

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