aggr on field not in chart

[#1]

Hi,

I need to average data at one level, then sum those avgs at the next level in a chart. The issue is the interim level is not in the chart.

Here is a sample of the data

State	City	Product	Cost	Year	Quantity
California	San Diego	A	50	2013	21
California	San Diego	B	40	2013	15
California	San Diego	C	25	2013	54
California	San Diego	A	50	2014	44
California	San Diego	B	40	2014	10
California	San Diego	C	25	2014	30
California	LA	B	40	2013	33
California	LA	D	75	2013	40
California	LA	E	65	2013	26
California	LA	B	40	2014	33
California	LA	D	75	2014	40
California	San Fran	A	50	2013	44
California	San Fran	F	35	2013	16
California	San Fran	A	50	2014	27
California	San Fran	F	35	2014	33
Ohio	Columbus	B	40	2013	12
Ohio	Columbus	D	75	2014	17
Ohio	Columbus	F	35	2014	33
Ohio	Toledo	E	65	2013	25
Ohio	Toledo	E	65	2014	57
Ohio	Cleveland	B	40	2013	22
Ohio	Cleveland	C	25	2013	32
Ohio	Cleveland	A	50	2014	18
Ohio	Cleveland	B	40	2014	34
Ohio	Cleveland	C	25	2014	23

The issue is I need to avg the sales by City, then sum those avgs at the State level. The end chart i want is showing Sales by State for current Year and last year (but not have the City dimension in the chart).

So far, my current & prior Year expressions are:

sum({$<Year={$(=ONLY(Year)-1)}>} Cost*Quantity ) / count({$<Year={$(=ONLY(Year)-1)}>} Product )

sum({$<Year={$(=ONLY(Year))}>} Cost*Quantity ) / count({$<Year={$(=ONLY(Year))}>} Product )

but that is just the averaging piece of the puzzle. I need to perform the avg per city, then sum per State (without the City being a chart dimension.

Any help would be appreciated,

R.

[SergeyMak · Accepted solution]

Try this

avg(Aggr(avg(Cost*Quantity),City,Year,State))/avg(Aggr(Count( Product),City,Year,State))

and see an example

[chiru_thota]

Are you expecting  Avg value at City level aggregated to State as shown in below example?

Ex    : California = 4529

           Ohio = 5865

I guess it will be easy if you can try that at data model level instead of set analysis by having separate column.

[#4]

Thanks Sergei,

I was able to use your solution. The main issue i was having was that one of the aggr fields isn't in the chart as a dimension, but it seems to work as long as one of the aggr fields is in the chart.

Thanks again,

Richard