topic Re: Avg(TimeValues) with not subsequent days in QlikView

Avg(TimeValues) with not subsequent days

Emmanuelle__Bustos — Mon, 16 Jun 2014 18:07:58 GMT

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)

=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.

Re: Avg(TimeValues) with not subsequent days

MK_QSL — Mon, 16 Jun 2014 18:43:41 GMT

Use

=Time(Avg([End Work Shift]),'hh:mm:ss')

Re: Avg(TimeValues) with not subsequent days

Emmanuelle__Bustos — Mon, 16 Jun 2014 18:52:18 GMT

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.

Re: Avg(TimeValues) with not subsequent days

rbecher — Mon, 16 Jun 2014 18:55:45 GMT

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

Re: Avg(TimeValues) with not subsequent days

Emmanuelle__Bustos — Mon, 16 Jun 2014 19:00:07 GMT

Is already attached.

Re: Avg(TimeValues) with not subsequent days

rbecher — Mon, 16 Jun 2014 19:37:21 GMT

The solution is:

=time(avg(frac(HORA_SALIDA_TIME)))

Since the field contains a whole timestamp value..

Re: Avg(TimeValues) with not subsequent days

rbecher — Mon, 16 Jun 2014 19:43:20 GMT

..or the other field:

=time(avg(frac(HORA_SALIDA_HHC)))

Re: Avg(TimeValues) with not subsequent days

Emmanuelle__Bustos — Mon, 16 Jun 2014 23:01:52 GMT

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.

Re: Avg(TimeValues) with not subsequent days

rbecher — Mon, 16 Jun 2014 23:09:06 GMT

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..

Re: Avg(TimeValues) with not subsequent days

Emmanuelle__Bustos — Mon, 16 Jun 2014 23:20:36 GMT

Yes I already get it Ralf,

The Time#() function was the necessary since the beginning. Once again thank you for your help!!!

Best Regards!