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,

       

      Pivot.bmp

      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