Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have an unwanted result with Avg() function using time values (End of Work Shift), it works fine when selecting one or multiple values but only if they are of subsequent days I mean selecting 2nd to 7th Jun days the function sends the right Average (Scenario 1) but if selecting values like 2nd to 7th and adding the 9th day excluding 8th) (Scenario 2) the resulting average is not the expected. I tried to use the time values as time and num format but the results are the same. I can't find a way to make this work, im even using a formula but works the same way:
Avg(HORA_SALIDA_HHC)
or
=SUM(HORA_SALIDA_HHC) / Count(DISTINCT FECHA_HH)
The Results desire and undesired are in the excel attached.
can someone please give me a hand to understand the unwanted result?
Attached a qvw sample, please select 02/06/14 to 09/06/14 from FECHA_HH listbox to match with the excel sample.
..or the other field:
=time(avg(frac(HORA_SALIDA_HHC)))
Use
=Time(Avg([End Work Shift]),'hh:mm:ss')
Hi Manish
Thanks for your response, but this formula only gives time format but the result is the same. The problem is not the format but the final result when select dates with not subsequent days.
Hi Emmanulle,
could you upload an example of the issue. I cannot recreate the issue with the Excel data. Interesting, QlikView calculates an average of 9:01:41 for the second scenario...
- Ralf
Is already attached.
The solution is:
=time(avg(frac(HORA_SALIDA_TIME)))
Since the field contains a whole timestamp value..
..or the other field:
=time(avg(frac(HORA_SALIDA_HHC)))
Hey Ralf Becher
It Works!! But stil don't get how the Frac() function does the trick since I know it returns the fraction part, so this means that the integer part is the other timestamp info?
If this is the case my doubt is:
In the script I load the field HORA_SALIDA as HORA_SALIDA (it is loaded as numeric format like 41792.377....)
so I load Time(HORA_SALIDA) as HORA_SALIDA_TIME (it is loaded as 9:04:12 AM ....)
and load l TimeStamp(HORA_SALIDA) as HORA_SALIDA_HHC (it is loaded as 06/06/2014 9:04:12 AM...)
since in the second load I used the Time() function for loading then I wasn't supposed to need the Frac() function in the Expression was I? If so is because even when using the Time() function the fields still load the integer part corresponding to the Full TimeStamp??
For now this is Solved thank you so much for your help and if you are able to clarify this last point I will be twice thankful.
Right, the fraction is the time part. One day = 1. You could also solve the problem during load using the Time#() function - Time() does only the formatting of the text representaion, doesn't touch the value; or Frac() itself. It's a bit confusing here that you see the text representaion of time only here, but the value is a full timestamp. It has cost me some time to find it out although it is a very simple problem..
Yes I already get it Ralf,
The Time#() function was the necessary since the beginning. Once again thank you for your help!!!
Best Regards!