aggregating

[#1]

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

[sunny_talwar]

May be this?

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

[#3]

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)

[swuehl · Accepted solution]

Or maybe

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

[#5]

Thanks swehi! Works perfectly!

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

[Saravanan_Desingh]

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)

[#7]

Thanks Saran, you are right. This works too.

[sunny_talwar]

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

[#9]

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.

[sunny_talwar]

Yes that's true as well