topic Intervall in QlikView

Intervall

master_student — Fri, 14 Oct 2016 09:34:52 GMT

Hello best community ever

I need to get the intervall between the two dates. The result would be in days/hours/minutes and seconds

What I am doing now :

Interval(date(DATE_DERNIERE_MODIF,'DD/MM/YYYY hh:mm:ss') -date(DATE_CREATION, 'DD/MM/YYYY hh:mm:ss'),'DD : hh : mm : ss')

but the result in days

I think the result in incorrect? isn't?

second question, I have 3900 second, I have to convert it in hours, any idea?

Thanks

Re: Intervall

adamdavi3s — Fri, 14 Oct 2016 10:38:50 GMT

Seconds is pretty easy... 3900/60/60

the interval should work like that AFAIK

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Re: Intervall

marcus_sommer — Fri, 14 Oct 2016 10:47:33 GMT

The result isn't correct because the 12-hour division of AM respectively PM aren't considered. Therefore try:

Interval(

timestamp(DATE_DERNIERE_MODIF,'DD/MM/YYYY hh:mm:ss TT')

- timetsamp(DATE_CREATION, 'DD/MM/YYYY hh:mm:ss TT'),

'DD : hh : mm : ss')

Maybe it worked without the timestamp like:

Interval(DATE_DERNIERE_MODIF - DATE_CREATION, 'DD : hh : mm : ss')

Your seconds could be converted into hours with:

round(3900 / (60 * 60), 0.01) = 1.08

- Marcus

Re: Intervall

adamdavi3s — Fri, 14 Oct 2016 10:50:05 GMT

Ah damn didn't spot the AM/PM issue

Re: Intervall

sunny_talwar — Fri, 14 Oct 2016 10:59:00 GMT

Marcus -

If this timestamps are already read as dual, do we need to use TimeStamp function before we use it with the interval? or did you wante to use TimeStamp#() function?

Interval(

TimeStamp#(DATE_DERNIERE_MODIF,'DD/MM/YYYY hh:mm:ss TT')

- TimeStamp#(DATE_CREATION, 'DD/MM/YYYY hh:mm:ss TT'),

'DD : hh : mm : ss')

Re: Intervall

marcus_sommer — Fri, 14 Oct 2016 12:00:48 GMT

Hi Sunny,

yes, you are right. If the values are dual the subtraction should work without any formatting and otherwise they should fail because strings couldn't be subtracted and would need a converting per timestamp#(). But it seems that it is different in this case if I look on the posted formula ...

- Marcus

Re: Intervall

master_student — Fri, 14 Oct 2016 12:04:45 GMT

Fot the interval, the issue is resolved. but to convert seconds into hours, there are some wrong result especially if the seconds<3600, look :

the formula I am using is : =time(Time#(DUREE_AVANT_CHANGEMENT,'s'))

Re: Intervall

sunny_talwar — Fri, 14 Oct 2016 12:08:11 GMT

May be this:

=Interval(Time#(DUREE_AVANT_CHANGEMENT,'s'), 'h:mm:ss')

Re: Intervall

adamdavi3s — Fri, 14 Oct 2016 12:08:35 GMT

Your formula is giving you the number of seconds from midnight

What do you want it to show?

Re: Intervall

master_student — Fri, 14 Oct 2016 13:15:13 GMT

Thanks a lot Sunny. Now I get the right results.