topic Re: Avg() Function in QlikView

Avg() Function

Thu, 24 Jan 2013 05:12:03 GMT

Hello Guys,

I just want to clarify how Avg() works. The first time I use avg, it is working properly. Giving me the right average like 3.4 or 3.1 etc.

But I have to include a 'historical' data in my application and boom! The avg goes 'gaga' and my output average goes beyond 800+++!

Even if I click year 2013, my average computation is 700! Why did it went like that? How do I find/resolve this problem?

Can you guys give me how it works? Anyone can explain?

Thanks a bunch!

Donna

Re: Avg() Function

er_mohit — Thu, 24 Jan 2013 05:16:29 GMT

try this

avg({<year={"$(=year)"}>}sales)

here sale is my fieldname.

you just put your fieldname there.

hope it helps

Re: Avg() Function

Thu, 24 Jan 2013 05:20:58 GMT

Sorry, still the same output 😞

Re: Avg() Function

er_mohit — Thu, 24 Jan 2013 05:28:00 GMT

m not sure why it didn't give you right answer. might be there duplicate values use distinct word.

avg(distinct{<year={"$(=year)"}>}sales).

hope it resolve your problem

Re: Avg() Function

Thu, 24 Jan 2013 07:21:55 GMT

still nope 😐

b.u.m.p~

Re: Avg() Function

Thu, 24 Jan 2013 10:05:12 GMT

sample app pls?

Re: Avg() Function

hic — Thu, 24 Jan 2013 14:45:11 GMT

The Avg() function works fine and returns the average per record. If you want the average per something else, e.g. Year you should use Sum(...) / Count(distinct Year)

You most likely have some records that have very large values. Find these by using the Max() function in a chart or in a statistics box.

HIC

Re: Avg() Function

Emmanuelle__Bustos — Mon, 16 Jun 2014 14:58:41 GMT

Hi hic

I have an issue like that, when use Avg() function using time value it works fine when selecting one or multiple values but only if they are continuous days i mean selecting 1st to 5th May it the function sends the right Average but if selecting values like 1st to 5th and adding the 7th (excluding 6th) the resulting average is beyond "Logic" i tried to use the time values as time and num value 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)

can you gime a hand to understand the wrong result?

Re: Avg() Function

hic — Mon, 16 Jun 2014 17:24:49 GMT

What do you mean "beyond Logic"? What do you get as result?

Could it be that it just loses the formatting? It so, try TimeStamp(Sum(HORA_SALIDA_HHC)/Count(distinct FECHA_HH)) .

HIC

Re: Avg() Function

Emmanuelle__Bustos — Mon, 16 Jun 2014 18:13:54 GMT

Hi Henric

Thanks for response I meant an "unwanted result" sorry my expression I opened a discussion trying to be more specific:

http://community.qlik.com/thread/122140

By the way I read your blog:

Average – Which average?

and tried the Avg( Aggr( Sum(Amount), MonthID ) )

changing the fields for my app fields of course and the result is the same as with the two formulas in my discussion.