Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP

Re: Total Sum hh:mm:ss not working

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

Highlighted
Creator
Creator

Re: Total Sum hh:mm:ss not working

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

Thanks!

Highlighted
MVP
MVP

Re: Total Sum hh:mm:ss not working

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

Highlighted
Creator
Creator

Re: Total Sum hh:mm:ss not working

error.JPG

Highlighted
Creator
Creator

Re: Total Sum hh:mm:ss not working

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

Highlighted
MVP
MVP

Re: Total Sum hh:mm:ss not working

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)

Highlighted
Creator
Creator

Re: Total Sum hh:mm:ss not working

THANKS A LOT!

YOU ARE A GENIUS!

Highlighted
Creator
Creator

Re: Total Sum hh:mm:ss not working

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