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:
=SUM(HORA_SALIDA_HHC) / Count(DISTINCTFECHA_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.
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..