topic Re: Wrong avg value in QlikView

Wrong avg value

inescastelhano — Mon, 04 Apr 2016 14:54:37 GMT

Hi all,

I'm facing a very strange issue. I have this expression:

=(time(avg({< _Type={2}>} [Hour 2]-[Hour 1])))

And I get this strange result:

If I sum all the values, the result is 21:11:00, so, I am pretty convinced that this avg value is wrong. I don't know what to do.

Can somebody help?

Thanks in advance!

Cheers,

Ines

Re: Wrong avg value

sunny_talwar — Mon, 04 Apr 2016 14:57:41 GMT

What about this:

=Time(Avg(Aggr((Time(Avg({< _Type={2}>} [Hour 2] - [Hour 1]))), ID, [Hour 1], [Hour 2])))

Re: Wrong avg value

swuehl — Mon, 04 Apr 2016 15:10:28 GMT

Where is your _Type field?

It's hard to compare your excel table screenshot with a calculation without knowing the full background.

And do you want to sum or average the intervals? You are using avg() in the expression but you are referencing a sum both in your manual calculation as well as the chart header.

Re: Wrong avg value

jlongoria — Mon, 04 Apr 2016 15:39:01 GMT

I think you are looking for the midpoint between Hour 1 and Hour 2. If I am correct, a problem I see with your expression is that avg(Hour 2 - Hour 1) is not the midpoint. The midpoint is (Hour 2 + Hour 1)/2.

Please try this:

=Time(avg(Aggr(([Hour2] + [Hour1])/2, ID, [Hour1], [Hour2])))

Re: Wrong avg value

hic — Tue, 05 Apr 2016 09:01:02 GMT

First, I'm not sure that using the Avg() function is the right approach. It may be better to use a sum and divide by a count. See Average – Which average?

Further, what you have in the picture is what you have in a QlikView chart. But these numbers are aggregated numbers and not necessarily the same as the numbers in source data. For instance, if your source data is

Hour1, Hour2

09:00, 10:00

10:00, 11:00

you will have a chart with just two lines (the distinct combinations):

Hour1, Hour2, Avg(Hour2-Hour1)

09:00, 10:00, 1:00

10:00, 11:00, 1:00

HIC

Re: Wrong avg value

Tue, 05 Apr 2016 12:18:13 GMT

What is it exactly you would like to achieve here?

Re: Wrong avg value

inescastelhano — Tue, 05 Apr 2016 13:03:45 GMT

Hi all, I found the reason. Qlik was returning a negative value for the last entry of the table, when converted to number (instead of time). Then, I found out this was misrepresenting the avg value.

Thank you all!