topic Re: aggregating in QlikView

aggregating

Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I have a problem if someone can help me with:

let us say i have the following table

City State Value A

Houston TX 10

Dallas TX 8

Belmont CA 12

Mountain View CA 6

now i have to use some formula where I compare the value at city level with average at State level, say

if value A for Houston (which is 10) is greater than average of state TX (which 9) then Value B 100.

I tried using aggr function but I think i am not able to use is correctly because if I do not give city as dimension, i only get calculation for one city and if I give city as dimension i obviously get average at the city level and not at state level.

thanks,

Manoj

Re: aggregating

sunny_talwar — Fri, 08 Apr 2016 00:16:06 GMT

May be this?

=Aggr(If(Avg(TOTAL <City> [Value A]) <= Avg([Value A]), 100), City, State)

Re: aggregating

Fri, 08 Apr 2016 00:38:34 GMT

Thanks Sunny but value B has to be at city level so it should be like

City State Value A Value B

Houston TX 10 100

Dallas TX 8 (not hundred since 8 is less than state average 9, say value is 90)

Belmont CA 12 100

Mountain View CA 7 (not hundred since 7 is less than state average 9.5, say value is 90)

(i have changed Values A slightly to avoid any confusion)

Re: aggregating

swuehl — Fri, 08 Apr 2016 00:46:15 GMT

Or maybe

City	=Only(Aggr(If(Avg(TOTAL <State> [Value A]) <= Avg([Value A]), 100), City, State))
	100
Belmont	100
Houston	100

Re: aggregating

Fri, 08 Apr 2016 02:07:17 GMT

Thanks swehi! Works perfectly!

I need to understand this aggr function better because I always have problems using it.

Re: aggregating

Saravanan_Desingh — Fri, 08 Apr 2016 02:27:48 GMT

Hi,

Here AGGR is not needed, if you follow the below:

Chart: Pivot, Straight

Dimension: State, City

Expression: If(Avg(TOTAL <State> [Value A]) < [Value A],100)

Re: aggregating

Fri, 08 Apr 2016 18:06:01 GMT

Thanks Saran, you are right. This works too.

Re: aggregating

sunny_talwar — Fri, 08 Apr 2016 18:09:34 GMT

Well the Aggr() would be needed if you need to fix the Total up top. For the current example, Only(Aggr()) or without Aggr() will give same answer of 100. But if the two values were 80 and 100, you would see a -. To get a sum, you can use Sum(Aggr()) or to get Avg, you can use Avg(Aggr())

Re: aggregating

Fri, 08 Apr 2016 18:22:01 GMT

Hi Saran,

I just checked and it seems that with your solution, I have to have State in the dimension whereas if I use aggr I don't need State in the dimension.

Re: aggregating

sunny_talwar — Fri, 08 Apr 2016 18:22:53 GMT

Yes that's true as well

Re: aggregating

Saravanan_Desingh — Fri, 08 Apr 2016 19:21:56 GMT

No words against Sunny

I agree..

Re: aggregating

sunny_talwar — Fri, 08 Apr 2016 19:23:39 GMT

You can always correct me if you think I am wrong. I will take your comment in a very sporty way my friend