Set analysis - set modifier to achieve cyclical mean by month

[william_ericksson]

Hi!

I'm trying to create a cyclical control chart with a moving mean value which varies seasonally. E.g. so for January 2017, I would like to plot the mean of January 2016, January 2015 and January 2014.

To start with, I have just tried to calculate the mean for each month across the whole dataset.

I started trying things like:

avg({<month={"=month"}>} TOTAL value)

But this isn't working. With more thought, I can see why - but I don't know how to progress.

I have got the initial idea to work by hard-coding the month numbers in (below):

The Blue line is:

sum(value)

The red line is:

if(month=01, avg({<month={01}>}total value),
  if(month=02, avg({<month={02}>}total value),
   if(month=03, avg({<month={03}>}total value),
    if(month=04, avg({<month={04}>}total value),
     if(month=05, avg({<month={05}>}total value),
      if(month=06, avg({<month={06}>}total value),
       if(month=07, avg({<month={07}>}total value),
        if(month=08, avg({<month={08}>}total value),
         if(month=09, avg({<month={09}>}total value),
          if(month=10, avg({<month={10}>}total value),
           if(month=11, avg({<month={11}>}total value),
            if(month=12, avg({<month={12}>}total value), ''      
          )
          )
         )
        )
       )
      )
     )      
     )
    )
   )
  )
)

Which produces this result:

Is there a way I could write this more efficiently?

I've attached the values in an Excel file.

If anyone can help, that would be greatly appreciated! Thanks in advance,

Will

[sunny_talwar · Accepted solution]

May be this

=Aggr(Avg(TOTAL <month> value), year_month, month)

[william_ericksson]

Thank you Sunny - that works perfectly!