topic Re: Calculate Interval Hours in QlikView

Calculate Interval Hours

Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I have a question regarding the time interval calculation in QlikView. I am using the following expression:

"Interval (sum ([hours worked]) - sum ([Total Business Hours]), 'hh: mm')" whose purpose is to calculate the balance of hours (bank of hours) of my company. However, if the balance exceeds 24:00, the value will be incorrect. If I have, for example:

Total Business Hours: 2659:05

Hours worked: 2856:00

My balance of hours would be: 196:55

But in my qvw appears: 2491:05

How can I solve this problem?

Regards,

Re: Calculate Interval Hours

vishsaggi — Tue, 23 Aug 2016 20:05:35 GMT

Your expression looks fine for me. Can you share a sample app to look further if possible ?

Re: Calculate Interval Hours

othniel2014 — Tue, 23 Aug 2016 20:13:31 GMT

works for me...

attach an application for extra help

Edit: within the pivot table works this otherwise

Time#(

Interval(sum(HoursWorked)-sum(BusinessHours), 'h: mm : ss')

,'h: mm : ss'

)

Re: Calculate Interval Hours

Tue, 23 Aug 2016 20:38:05 GMT

The image bellow show:

Carga Horária: Interval(Sum([Horas Úteis - Total Horas])*count([Funcionário - Nome]),'HH:mm')

Horas Trabalhadas: Interval(sum([Ponto - Horas Trabalhadas]),'HH:mm')

and

Saldo de Horas: Interval(sum([Ponto - Horas Trabalhadas])-sum([Horas Úteis - Total Horas]), 'hh:mm')

You can see that if subtract "Carga Horária" to "Horas Trabalhadas", the difference is: 196:55. But, shows 2491:05 in "Saldo de Horas".

Re: Calculate Interval Hours

Tue, 23 Aug 2016 20:42:44 GMT

Fine Othniel Coronado,

But if you put some values and use sum function the problem happens.

Thanks for your help

Re: Calculate Interval Hours

santiago_respane — Tue, 23 Aug 2016 21:04:19 GMT

Please share a sample app in order to help you better.

Tried you expression in various scenarios and it worked perfectly, with and without the sum and changing the values order.

Maybe there's something with your field format.

Kind regards,

Re: Calculate Interval Hours

vishsaggi — Tue, 23 Aug 2016 21:05:57 GMT

Try this and let me know:

Saldo de Horas: = Interval( [Horas Trabalhadas] - [Carga Horária]), 'hh:mm')

Re: Calculate Interval Hours

vishsaggi — Tue, 23 Aug 2016 21:08:19 GMT

Carga Horária: Interval(Sum([Horas Úteis] - [Total Horas])*count([Funcionário] - [Nome]),'HH:mm')

Horas Trabalhadas: Interval(sum([Ponto] - [Horas Trabalhadas]),'HH:mm')

Try to add this to your field names. We do not know how your field names are named.

Re: Calculate Interval Hours

Tue, 23 Aug 2016 21:11:58 GMT

I realized an important information. If I select just one professional the balance of hours it's right! But if I select more then one or all of professionals the balance of hours the problem happens.

Re: Calculate Interval Hours

Tue, 23 Aug 2016 21:17:07 GMT

Vish Nagaraju,

The right name of the fields is "Horas Úteis - Total Horas", "Funcionário - Nome", "Ponto - Horas Trabalhadas" so I can't put "[" as you said.

Thanks for your your help.

Re: Calculate Interval Hours

Tue, 23 Aug 2016 21:17:53 GMT

Not works!

Re: Calculate Interval Hours

othniel2014 — Tue, 23 Aug 2016 21:26:50 GMT

What filters used for Appears: 2491: 05?

Re: Calculate Interval Hours

Tue, 23 Aug 2016 21:38:05 GMT

othniel2014‌

You put a answer bellow:

Try:

=Interval(

Interval(Sum([Horas Úteis - Total Horas])*count([Funcionário - Nome]),'HH:mm')

Interval(sum([Ponto - Horas Trabalhadas]),'HH:mm')

,'hh:mm')

This answer solved the problem. But I couldn't see anymore.

Re: Calculate Interval Hours

othniel2014 — Tue, 23 Aug 2016 21:42:32 GMT

=Interval(

Interval(Sum([Horas Úteis - Total Horas])*count([Funcionário - Nome]),'HH:mm')

Interval(sum([Ponto - Horas Trabalhadas]),'HH:mm')

,'hh:mm')