13 Replies Latest reply: Jun 14, 2012 9:34 AM by Tjeerd Wieberdink RSS

    What's the aggr dimension value for the highest value of expression

      Thanks to some earlier help from Daniel Rozental I have the following expression which gives me the highest (max) value of sum(SalesAmount) over the last 12 periods or [$Year Month]s if ploted on a chart. with [$Year Month] as the dimension.

      I now want to know which [$Year Month] this max value corresponds to?

      =max(
      {$<PeriodCounter = {">$(#=max(PeriodCounter-12))<=$(#=max(PeriodCounter))"},[$Year Month]= >} 
           aggr(
                sum({$<PeriodCounter = {">$(#=max(PeriodCounter-12))<=$(#=max(PeriodCounter))"},[$Year Month]= >} 
                     [SalesAmount]
                      )
           ,[$Year Month])
       )
      


      Month [SalesAmount]
      2011M07 £50841
      2011M06 £5364840 - this is the highest value - so I need the anwer 2011M06
      2011M05 £5193593
      2011M04 £3813374
      2011M03 £4220276
      2011M02 £4163247
      2011M01 £3002792
      2010M12 £5242737
      2010M11 £4680143
      2010M10 £3362565
      2010M09 £5057832
      2010M08 £5060385

       

      Any help much appreciated
      Regards
      Matthew