Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2010-11-25 10:56 AM

percentual de horas

Ola,

tenho as informaçoes abaixo, onde o tempo utilizado consegui atraves da função =sum(time(hora_fim-hora_inicio)) e as horas_mes é um valor decimal, porem tentando achar o aproveitamento (=sum((hora_fim-hora_inicio))/(horas)) retorna o valor 2,03 pois 17:20 equivale a 3,7222

Tempo utilizadoHoras MesAproveitamento
17:20:00183,252,03%
17:20:00183,252,03%


Como resolvo isto ?

Agradeço qualquer auxilio.

590 Views
0 Likes
1 Solution

Accepted Solutions
Caique_Zaniolo
Employee
Employee
‎2010-12-02 03:18 PM

In response to

Ola Marcos,

não tinha visto direito a sua tabela. Me pareceu uma Straight table porque vc tinha as linhas repetidas. Só depois que vc falou que estava inativa que vi as linhas de subtotal Stick out tongue

No aggr externo vc já tentou colocar os campos que tem na estrutura (colaborador, mes, cd_veiculo)?

View solution in original post

455 Views
0 Likes
13 Replies
Not applicable
‎2010-12-01 12:42 PM
Author

Olá Marcos,

Recomendo a você trazer a informação da hora em formato numérico puro.

Ex: 01:00 é igual a 0,041666666666667

Depois vc faz a seguinte expressão Interval(Sum(Hora)) para obter o total da horas.

PS. tenho um exemplo e não estou conseguindo anexar o QVW.

Abraços.

455 Views
0 Likes
Not applicable
‎2010-12-02 01:34 PM
Author

In response to

Fernando, fiz conforme sugeriu e tive sucesso. Muitíssio obrigado !

Agora abusando, (enquanto não inicia o curso dia 13), utilizo as horas para verificar o aproveitamento do colaborador relativo ao tempo de efetivação dos serviços com o tempo que o mesmo tem disponivel no mes (horario do ponto) e para tal, tentei utilizar o sum (total<colaborador>horas) e até que funcionou na linha do serviço, porem nos totais deu problema pois somou tudo. Para que entenda melhor, coloco abaixo a visão. Observe funciona legal até chegar ao totais, quando então não quebra no mes, faz a soma dos 2 meses juntos.

empresamesColaboradorcd_veiculoOSNro OrdensTempoHoras MesAproveitamento
3,00ago4781A073473891217:40:00175:55:124,36%
3,00ago4781V000374054816:55:00175:55:123,93%
3,00ago4781V070174028615:40:00175:55:123,22%
3,00ago4781Total
320:15:00175:55:1211,51%
3,00ago5330A071773858510:30:00190:34:480,26%
3,00ago5330A073573930517:10:00190:34:483,76%
3,00ago5330V000374054817:04:00190:34:483,71%
3,00ago5330V000974054913:10:00190:34:481,66%
3,00ago5330V001074016811:30:00190:34:480,79%
3,00ago5330V070174028615:40:00190:34:482,97%
3,00ago5330V070274015214:55:00190:34:482,58%
3,00ago5330V070374002510:40:00190:34:480,35%
3,00ago5330V070973893211:20:00190:34:480,70%
3,00ago5330V071073942812:00:00190:34:481,05%
3,00ago5330V071573874214:30:00190:34:482,36%
3,00ago5330V071673942919:15:00190:34:484,85%
3,00ago5330V071873969215:20:00190:34:482,80%
3,00ago5330V073073877315:00:00190:34:482,62%
3,00ago5330V073973929112:55:00190:34:481,53%
3,00ago5330Total
1560:59:00190:34:4832,00%
3,00ago5509V000374054817:04:00190:34:483,71%
3,00ago5509V070174028615:40:00190:34:482,97%
3,00ago5509Total
212:44:00190:34:486,68%
3,00agoTotal

1693:58:001251:57:367,51%
3,00set


416:55:001251:57:361,35%
3,00Total


20110:53:001251:57:368,86%


Tentei a funcão aggr (interval(aggr( sum(horas), colaborador, mes, cd_veiculo )) porem com ela não faz a totalização no mes, conforme abaixo:

empresamesColaboradorcd_veiculoOSNro OrdensTempoHoras MesAproveitamento
3,00ago4781A073473891217:40:00175:55:124,36%
3,00ago4781V000374054816:55:00175:55:123,93%
3,00ago4781V070174028615:40:00175:55:123,22%
3,00ago4781Total
320:15:00175:55:1211,51%
3,00ago5330A071773858510:30:00190:34:480,26%
3,00ago5330A073573930517:10:00190:34:483,76%
3,00ago5330V000374054817:04:00190:34:483,71%
3,00ago5330V000974054913:10:00190:34:481,66%
3,00ago5330V001074016811:30:00190:34:480,79%
3,00ago5330V070174028615:40:00190:34:482,97%
3,00ago5330V070274015214:55:00190:34:482,58%
3,00ago5330V070374002510:40:00190:34:480,35%
3,00ago5330V070973893211:20:00190:34:480,70%
3,00ago5330V071073942812:00:00190:34:481,05%
3,00ago5330V071573874214:30:00190:34:482,36%
3,00ago5330V071673942919:15:00190:34:484,85%
3,00ago5330V071873969215:20:00190:34:482,80%
3,00ago5330V073073877315:00:00190:34:482,62%
3,00ago5330V073973929112:55:00190:34:481,53%
3,00ago5330Total
1560:59:00190:34:4832,00%
3,00ago5509V000374054817:04:00190:34:483,71%
3,00ago5509V070174028615:40:00190:34:482,97%
3,00ago5509Total
212:44:00190:34:486,68%
3,00agoTotal

1693:58:00-7,51%
3,00set


416:55:00-1,35%
3,00Total


20110:53:00-8,86%


e caso coloque um sum antes da função, totaliza tudo.

Novamente obrigado pelo retorno anterior;

455 Views
0 Likes
Caique_Zaniolo
Employee
Employee
‎2010-12-02 02:01 PM

In response to

Veja o que vc tem configurado na expressão em Total Mode. Deve estar Sum

Use Expression ou escolha uma forma de cálculo que satisfaça a sua exigência.

Abraços

455 Views
0 Likes
Not applicable
‎2010-12-02 02:14 PM
Author

In response to Caique_Zaniolo

Ola Caique,

esta sinalizado como "total da expressao" e fica desabilitado, sem que eu possa selecionar as demais opçoes.



455 Views
0 Likes
Not applicable
‎2010-12-02 02:45 PM
Author

In response to

ola marcos, acredito que colocando a funcao aggr dentro da funcao sum ja deva resolver...

sum(... aggr(... ) )

455 Views
0 Likes
Not applicable
‎2010-12-02 02:55 PM
Author

In response to

Oi Fabio, conforme relatei no post anterior, se coloco o sum, totaliza tudo, acumulando o tempo mensal a cada serviço efetuado.

empresamesColaboradorcd_veiculoOSNro OrdensTempoHoras MesAproveitamento
3,00ago4781A073473891217:40:00175:55:124,36%
3,00ago4781V000374054816:55:00175:55:123,93%
3,00ago4781V070174028615:40:00175:55:123,22%
3,00ago4781Total
320:15:00527:45:3611,51%
3,00ago5330A071773858510:30:00190:34:480,26%
3,00ago5330A073573930517:10:00190:34:483,76%
3,00ago5330V000374054817:04:00190:34:483,71%
3,00ago5330V000974054913:10:00190:34:481,66%
3,00ago5330V001074016811:30:00190:34:480,79%
3,00ago5330V070174028615:40:00190:34:482,97%
3,00ago5330V070274015214:55:00190:34:482,58%
3,00ago5330V070374002510:40:00190:34:480,35%
3,00ago5330V070973893211:20:00190:34:480,70%
3,00ago5330V071073942812:00:00190:34:481,05%
3,00ago5330V071573874214:30:00190:34:482,36%
3,00ago5330V071673942919:15:00190:34:484,85%
3,00ago5330V071873969215:20:00190:34:482,80%
3,00ago5330V073073877315:00:00190:34:482,62%
3,00ago5330V073973929112:55:00190:34:481,53%
3,00ago5330Total
1560:59:002858:42:0032,00%
3,00ago5509V000374054817:04:00190:34:483,71%
3,00ago5509V070174028615:40:00190:34:482,97%
3,00ago5509Total
212:44:00381:09:366,68%
3,00agoTotal

1693:58:003767:37:127,51%
3,00set


416:55:001039:23:241,35%
3,00Total


20110:53:004807:00:368,86%


455 Views
0 Likes
Caique_Zaniolo
Employee
Employee
‎2010-12-02 03:18 PM

In response to

Ola Marcos,

não tinha visto direito a sua tabela. Me pareceu uma Straight table porque vc tinha as linhas repetidas. Só depois que vc falou que estava inativa que vi as linhas de subtotal Stick out tongue

No aggr externo vc já tentou colocar os campos que tem na estrutura (colaborador, mes, cd_veiculo)?

456 Views
0 Likes
Not applicable
‎2010-12-03 11:25 AM
Author

In response to Caique_Zaniolo

Oi Caique, como assim aggr exteno ??

455 Views
0 Likes
Not applicable
‎2010-12-03 03:01 PM
Author

In response to

Marcos, pode mandar o QVW para mim?

Abraços.

Tonial.

455 Views
0 Likes