topic Average formula in QlikView

Average formula

jvitantonio — Fri, 28 Oct 2011 13:25:46 GMT

hi, I have the following:

Product	Value	Average
Product1	40	36
Product2	32	36

Value field is a formula, so it is NOT a field, it's calculated on the pivot expression. The average field is why I need. I tried avg(formula) but it's giving me no value. OF course this should be dinamyc according to the rows. Help?

Average formula

swuehl — Fri, 28 Oct 2011 13:32:17 GMT

Hi,

so what's your formula alike? Do you use an aggregation function in your formula?

If so, try advanced aggregation, like

= avg( aggr( formula, Product))

Hope this helps,

Stefan

Average formula

jvitantonio — Fri, 28 Oct 2011 13:47:07 GMT

Hi,

My formula is a division between 2 counts with a condition like:

count({$<Field1={"=LEN(Field1)>0"} >}Id)/count( {$<Field1={"=LEN(Field1)>0"} >} Id2 )

Average formula

swuehl — Fri, 28 Oct 2011 13:51:23 GMT

I see, and count is an aggregation function, therefore you have to use advanced aggregation with avg.

Have you tried above?

Average formula

jvitantonio — Fri, 28 Oct 2011 14:06:32 GMT

I tried that but I have the same value. May be it's applying the average only to the same row??

Average formula

Fri, 28 Oct 2011 14:09:35 GMT

This might work:

avg(TOTAL aggr(count({$<Field1={"=LEN(Field1)>0"} >}Id)/count( {$<Field1={"=LEN(Field1)>0"} >} Id2), Product))

Kiran.

Average formula

jvitantonio — Fri, 28 Oct 2011 14:15:39 GMT

Hey Kiran, you get it right. Thanks!!