Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
inescastelhano
Partner - Creator II
Partner - Creator II

Wrong avg value

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:

qlik_help.PNG

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

1 Solution

Accepted Solutions
inescastelhano
Partner - Creator II
Partner - Creator II
Author

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!

View solution in original post

6 Replies
sunny_talwar

What about this:

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

swuehl
MVP
MVP

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.

jlongoria
Creator
Creator

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])))

hic
Former Employee
Former Employee

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

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

Not applicable

Hi

What is it exactly you would like to achieve here?

inescastelhano
Partner - Creator II
Partner - Creator II
Author

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!