
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you used Interval(Sum(FIELD),'hh:mm:ss') to format the sum accordingly?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Stefan, the FIELD that you mention is the decimal or the hh:mm:ss?
Thanks!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
THANKS A LOT!
YOU ARE A GENIUS!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
