topic Average Expression in QlikView

Average Expression

Thu, 22 Aug 2013 11:43:53 GMT

Hi,

I have the following data:

Category Attribute Value

A one 1

B two 4

C one 2

D three 7

E two 2

F one 3
G three 7

What would be the correct expression if i would like to calculate the average of all data which have the Attribute "one". So the result would be (1+2+3)/3=2, for "two":(4+2)/2=3 and for "three"=(7+7)/2 =7?

So what would be the correct expression in case the average needs to be calculated based on a selection of a Category?

And what would be the correct expression in case the average is supposed to be calculated on all available data no matter which Categories are selected in another listbox..?

Thank you for your help!

Re: Average Expression

alexandros17 — Thu, 22 Aug 2013 11:48:09 GMT

sum({$ <Attribute={'one'}>} Value)

Re: Average Expression

Gysbert_Wassenaar — Thu, 22 Aug 2013 11:48:31 GMT

That would be avg({<Attribute={'One'}>} Value) if you want to use fixed value. If you want to select an attribute then it's simply avg(Value). If you want to ignore selections of categories it becomes avg({<Category=>} Value)

Re: Average Expression

alexandros17 — Thu, 22 Aug 2013 11:58:29 GMT

Obviously is avg and not sum ... sorry

Re: Average Expression

Thu, 22 Aug 2013 12:08:47 GMT

Thank you for the quick response!!

Re: Average Expression

Thu, 22 Aug 2013 14:21:45 GMT

Hi,

ok I tried your solution with ignoring the selected categories.

BUT: when I use the avg({<Category=>} Value) in combination with avg({<Attribute={'One'}>} Value) it calculates the average over all and not only over the attribute='one'-values...

so my expression is:

avg({<Attribute={'One'}>} {<Category=>} Value)

which in fact calculates avg({<Category=>} Value)

I guess this is because the [Category] and the [Attribute] are connected within the QlikView-Logic...

Does anyone have a solution for that?

Thanks

Re: Average Expression

Gysbert_Wassenaar — Thu, 22 Aug 2013 14:25:43 GMT

Try this if you want to average of the values over all categories, but only where attribute is 'One': avg({<Attribute={'One'}, Category=>} Value)