Combining AGGR and Set Analysis

[#1]

Hi all,

I have been struggling trying to combine AGGR function with Set Analysis and wondered if there is a simplier way to do things.

I have been using the following function to calculate the Average weekly totals for a particular calendar month. (this allows us to compare year on year totals when months vary in the number of weeks on each month. eg 4 or five weeks)

=AVG(AGGR(Sum([In Count]), Week, Month, Year))

This expression works fine and gives me the data I require as in the Pivot table below,

What I would like to do now is show the percetage increase / decrease from the previous year. Simple hey! but do you think I can get this to work?

I think from reading the manual I am not allowed to have expressions in the AGGR function so cant do things like.

=(AVG(AGGR(Sum([In Count]), Week, Month, Year)) - AVG(AGGR(Sum([In Count]), Week, Month, {$<Year={2011}>})) )/ AVG(AGGR(Sum([In Count]), Week, Month, Year))

Anybody got any idears how I can overcome this issue.

Many thanks

Lawrence

[SunilChauhan]

please see the sheet3 in attached file

[#3]

Hi Suni,

Thanks very much for you reply and sorry for not getting back to you sooner. following my post my appendix ruptured so have been away from work for some time!

Your example nealy gives me what I need, howver why does it only show one site name. even if you remove all filters?

Many thanks,

Lawrence