2 Replies Latest reply: Oct 20, 2011 9:41 AM by Lawrence Chapman RSS

    Combining AGGR and Set Analysis

      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