Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jumiprado
Creator
Creator

Total Sum hh:mm:ss not working

Hey guys!  i have the following problem, i have a table where i calculate from decimals fields the time in hh:mm:ss and when i make a table the total sum not working because the decimal total is 107,6 but for hh:mm:ss show other thing!

      

FechaHs. LaborablesHs. TrabajadasHs. Trabajadas S.Dif.Dif. S.
01/01/20180,00,000:00:00N/A00:00:00
02/01/20187,09,209:14:002,202:14:00
03/01/20187,09,809:45:002,802:45:00
04/01/20187,09,809:49:002,802:49:00
05/01/20187,09,509:29:002,502:29:00
06/01/20180,00,000:00:00N/A00:00:00
07/01/20180,00,000:00:00N/A00:00:00
08/01/20187,013,513:32:006,506:32:00
09/01/20187,013,313:18:006,306:18:00
10/01/20187,013,413:23:006,406:23:00
11/01/20187,013,313:20:006,306:20:00
12/01/20187,09,309:18:002,302:18:00
13/01/20180,00,000:00:00N/A00:00:00
14/01/20180,00,000:00:00N/A00:00:00
15/01/20187,013,313:20:006,306:20:00
16/01/20187,012,512:30:005,505:30:00
17/01/20187,09,809:50:002,802:50:00
18/01/20187,012,912:51:005,905:51:00
19/01/20187,09,709:41:002,702:41:00
20/01/20180,00,000:00:00N/A00:00:00
21/01/20180,00,000:00:00N/A00:00:00
22/01/20187,013,313:17:006,306:17:00
23/01/20187,013,613:37:006,606:37:00
24/01/20187,013,013:01:006,006:01:00
25/01/20187,013,113:04:006,106:04:00
26/01/20187,010,009:58:003,002:58:00
27/01/20180,00,000:00:00N/A00:00:00
28/01/20180,00,000:00:00N/A00:00:00
29/01/20187,012,612:34:005,605:34:00
30/01/20187,013,113:05:006,106:05:00
31/01/20187,013,713:42:006,706:42:00
Total154,0261,621:38:00107,611:38:00
154,0261,621:38:00107,611:38:00

Thanks!

@sunnyT

8 Replies
swuehl
MVP
MVP

Have you used Interval(Sum(FIELD),'hh:mm:ss') to format the sum accordingly?

jumiprado
Creator
Creator
Author

Hi Stefan, the FIELD that you mention is the decimal or the hh:mm:ss?

Thanks!

swuehl
MVP
MVP

If you use the decimal, you need to transform to the QV time format (fractions of a day):

Interval(Sum([Dif.])/24 ,'hh:mm:ss')

jumiprado
Creator
Creator
Author

error.JPG

jumiprado
Creator
Creator
Author

The expression for Dif. is :

if( sum( {< Tipo_Dia = {'Laborable'}>} distinct Intervalo_Decimal) =0,'N/A',

if(sum( {<Tipo_Dia={'Laborable'}>} Ausencia_injustificada)=0 ,

[Hs. Trabajadas] - [Hs. Laborables],'N/A'))

swuehl
MVP
MVP

And how did you calculate the Dif. S. column? Just use Interval() function on this expression.

Or using your last one

Interval(

(

if( sum( {< Tipo_Dia = {'Laborable'}>} distinct Intervalo_Decimal) =0,'N/A',

  if(sum( {<Tipo_Dia={'Laborable'}>} Ausencia_injustificada)=0 ,

  [Hs. Trabajadas] - [Hs. Laborables],'N/A'))

) / 24

,'hh:mm:ss')

edit: added the division by 24 to transform the numbers in hours to the internal QV date and time format (fraction of days)

jumiprado
Creator
Creator
Author

THANKS A LOT!

YOU ARE A GENIUS!

jumiprado
Creator
Creator
Author

Hi Stefan, i have a new issue with this, in some cases the expression is ok but in other not. let me show you an example.

         

AñoMesFechaDíaTipo_DiaHs. LaborablesHs. TrabajadasHs. Trabajadas S.Dif.Dif. S.
2017dic.01/12/2017vie.Laborable8,04,24:10:00-3,80:00:00
2017dic.02/12/2017sáb.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.03/12/2017dom.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.04/12/2017lun.Laborable8,09,09:02:001,01:02:00
2017dic.05/12/2017mar.Laborable8,08,07:59:000,00:00:00
2017dic.06/12/2017mié.Laborable8,05,65:33:00-2,50:00:00
2017dic.07/12/2017jue.Laborable8,08,28:12:000,20:12:00
2017dic.08/12/2017vie.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.09/12/2017sáb.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.10/12/2017dom.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.11/12/2017lun.Laborable8,07,37:15:00-0,80:00:00
2017dic.12/12/2017mar.Laborable8,07,87:45:00-0,30:00:00
2017dic.13/12/2017mié.Laborable8,08,08:00:000,00:00:00
2017dic.14/12/2017jue.Laborable0,00,00:00:00N/A0:00:00
2017dic.15/12/2017vie.Laborable8,08,38:17:000,30:17:00
2017dic.16/12/2017sáb.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.17/12/2017dom.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.18/12/2017lun.Laborable8,08,08:02:000,00:02:00
2017dic.19/12/2017mar.Laborable0,00,00:00:00N/A0:00:00
2017dic.20/12/2017mié.Laborable8,07,97:51:00-0,20:00:00
2017dic.21/12/2017jue.Laborable8,07,97:56:00-0,10:00:00
2017dic.22/12/2017vie.Laborable8,08,48:23:000,40:23:00
2017dic.23/12/2017sáb.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.24/12/2017dom.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.25/12/2017lun.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.26/12/2017mar.Laborable8,08,07:57:000,00:00:00
2017dic.27/12/2017mié.Laborable8,08,28:10:000,20:10:00
2017dic.28/12/2017jue.Laborable8,08,28:11:000,20:11:00
2017dic.29/12/2017vie.Laborable8,08,58:30:000,50:30:00
2017dic.30/12/2017sáb.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.31/12/2017dom.No Laborable/Feriado0,00,00:00:00N/A0:00:00
2017dic.Total136,0131,2131:13:00-4,80:00:00
2017Total136,0131,2131:13:00-4,80:00:00

The expression for Dif. S. is:

IF(INTERVAL((sum( {< Tipo_Dia = {'Laborable'}, Dia_Licenciado = {0}, Ausencia_injustificada = {0}>}  Carga_Horaria)/24),'hh:mm:ss')>

INTERVAL((sum( {< Tipo_Dia = {'Laborable'}, Dia_Licenciado = {0}, Ausencia_injustificada = {0}>} Intervalo_Decimal)/24),'hh:mm:ss'),INTERVAL((sum( {< Tipo_Dia = {'Laborable'}, Dia_Licenciado = {0}, Ausencia_injustificada = {0}>}  Carga_Horaria)/24)*0,'hh:mm:ss'),

INTERVAL(INTERVAL((sum( {< Tipo_Dia = {'Laborable'}, Dia_Licenciado = {0}, Ausencia_injustificada = {0}>} Intervalo_Decimal)/24),'hh:mm:ss')-INTERVAL((sum( {< Tipo_Dia = {'Laborable'}, Dia_Licenciado = {0}, Ausencia_injustificada = {0}>}  Carga_Horaria)/24),'hh:mm:ss'),'hh:mm:ss'))

I need to sum only the positive values so, in this example the result should be

2:47:00

Thanks!

swuehl

stalwar1