topic Re: Total Sum hh:mm:ss not working in QlikView

Total Sum hh:mm:ss not working

jumiprado — Thu, 13 Sep 2018 13:43:50 GMT

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!

Fecha	Hs. Laborables	Hs. Trabajadas	Hs. Trabajadas S.	Dif.	Dif. S.
01/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
02/01/2018	7,0	9,2	09:14:00	2,2	02:14:00
03/01/2018	7,0	9,8	09:45:00	2,8	02:45:00
04/01/2018	7,0	9,8	09:49:00	2,8	02:49:00
05/01/2018	7,0	9,5	09:29:00	2,5	02:29:00
06/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
07/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
08/01/2018	7,0	13,5	13:32:00	6,5	06:32:00
09/01/2018	7,0	13,3	13:18:00	6,3	06:18:00
10/01/2018	7,0	13,4	13:23:00	6,4	06:23:00
11/01/2018	7,0	13,3	13:20:00	6,3	06:20:00
12/01/2018	7,0	9,3	09:18:00	2,3	02:18:00
13/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
14/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
15/01/2018	7,0	13,3	13:20:00	6,3	06:20:00
16/01/2018	7,0	12,5	12:30:00	5,5	05:30:00
17/01/2018	7,0	9,8	09:50:00	2,8	02:50:00
18/01/2018	7,0	12,9	12:51:00	5,9	05:51:00
19/01/2018	7,0	9,7	09:41:00	2,7	02:41:00
20/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
21/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
22/01/2018	7,0	13,3	13:17:00	6,3	06:17:00
23/01/2018	7,0	13,6	13:37:00	6,6	06:37:00
24/01/2018	7,0	13,0	13:01:00	6,0	06:01:00
25/01/2018	7,0	13,1	13:04:00	6,1	06:04:00
26/01/2018	7,0	10,0	09:58:00	3,0	02:58:00
27/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
28/01/2018	0,0	0,0	00:00:00	N/A	00:00:00
29/01/2018	7,0	12,6	12:34:00	5,6	05:34:00
30/01/2018	7,0	13,1	13:05:00	6,1	06:05:00
31/01/2018	7,0	13,7	13:42:00	6,7	06:42:00
Total	154,0	261,6	21:38:00	107,6	11:38:00
	154,0	261,6	21:38:00	107,6	11:38:00

Thanks!

@sunnyT

Re: Total Sum hh:mm:ss not working

swuehl — Thu, 13 Sep 2018 13:47:18 GMT

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

Re: Total Sum hh:mm:ss not working

jumiprado — Thu, 13 Sep 2018 14:02:12 GMT

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

Thanks!

Re: Total Sum hh:mm:ss not working

swuehl — Thu, 13 Sep 2018 14:14:24 GMT

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

Re: Total Sum hh:mm:ss not working

jumiprado — Thu, 13 Sep 2018 15:45:18 GMT

Re: Total Sum hh:mm:ss not working

jumiprado — Thu, 13 Sep 2018 15:46:00 GMT

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

Re: Total Sum hh:mm:ss not working

swuehl — Thu, 13 Sep 2018 17:02:17 GMT

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)

Re: Total Sum hh:mm:ss not working

jumiprado — Thu, 13 Sep 2018 18:30:10 GMT

THANKS A LOT!

YOU ARE A GENIUS!

Re: Total Sum hh:mm:ss not working

jumiprado — Tue, 18 Sep 2018 15:07:03 GMT

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ño	Mes	Fecha	Día	Tipo_Dia	Hs. Laborables	Hs. Trabajadas	Hs. Trabajadas S.	Dif.	Dif. S.
2017	dic.	01/12/2017	vie.	Laborable	8,0	4,2	4:10:00	-3,8	0:00:00
2017	dic.	02/12/2017	sáb.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	03/12/2017	dom.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	04/12/2017	lun.	Laborable	8,0	9,0	9:02:00	1,0	1:02:00
2017	dic.	05/12/2017	mar.	Laborable	8,0	8,0	7:59:00	0,0	0:00:00
2017	dic.	06/12/2017	mié.	Laborable	8,0	5,6	5:33:00	-2,5	0:00:00
2017	dic.	07/12/2017	jue.	Laborable	8,0	8,2	8:12:00	0,2	0:12:00
2017	dic.	08/12/2017	vie.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	09/12/2017	sáb.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	10/12/2017	dom.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	11/12/2017	lun.	Laborable	8,0	7,3	7:15:00	-0,8	0:00:00
2017	dic.	12/12/2017	mar.	Laborable	8,0	7,8	7:45:00	-0,3	0:00:00
2017	dic.	13/12/2017	mié.	Laborable	8,0	8,0	8:00:00	0,0	0:00:00
2017	dic.	14/12/2017	jue.	Laborable	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	15/12/2017	vie.	Laborable	8,0	8,3	8:17:00	0,3	0:17:00
2017	dic.	16/12/2017	sáb.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	17/12/2017	dom.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	18/12/2017	lun.	Laborable	8,0	8,0	8:02:00	0,0	0:02:00
2017	dic.	19/12/2017	mar.	Laborable	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	20/12/2017	mié.	Laborable	8,0	7,9	7:51:00	-0,2	0:00:00
2017	dic.	21/12/2017	jue.	Laborable	8,0	7,9	7:56:00	-0,1	0:00:00
2017	dic.	22/12/2017	vie.	Laborable	8,0	8,4	8:23:00	0,4	0:23:00
2017	dic.	23/12/2017	sáb.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	24/12/2017	dom.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	25/12/2017	lun.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	26/12/2017	mar.	Laborable	8,0	8,0	7:57:00	0,0	0:00:00
2017	dic.	27/12/2017	mié.	Laborable	8,0	8,2	8:10:00	0,2	0:10:00
2017	dic.	28/12/2017	jue.	Laborable	8,0	8,2	8:11:00	0,2	0:11:00
2017	dic.	29/12/2017	vie.	Laborable	8,0	8,5	8:30:00	0,5	0:30:00
2017	dic.	30/12/2017	sáb.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	31/12/2017	dom.	No Laborable/Feriado	0,0	0,0	0:00:00	N/A	0:00:00
2017	dic.	Total			136,0	131,2	131:13:00	-4,8	0:00:00
2017	Total				136,0	131,2	131:13:00	-4,8	0: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‌