Previous Year Avg Line on Current Year Bar Graph

[#1]

Hello All,

    I'm having a problem getting an average line from the previous year on to my current year bar graph.  Currently, I have a bar graph with dimension MonthYear that counts the occurences of sales during that month and graphs accordingly (Jan 2013-Dec 2013).  In order to set a goal, I'd like to have a straight line indicating the average sales per month for 2012 in hopes that each bar in 2013 is higher than the 2012 line.

    Additionaly, if possible I'd like to keep a running average line.  Therefore, the data point in March 2013 would be (January + February + March) / 3.

    Lastly, I need the lines to stay static regardless of the filters.  So, if just the last two quarters of 2013 are selected, I'd like to keep the running total line calculating as if all of the months were calculated instead of just starting in July.

I hope this all makes sense and thanks in advance for your help!

Bryan

[#2]

Hi,

Best solution will depend on the nature of your fields.

If you have a date field, Month(DateField) can be your first dimension and Year(DateField) your second. This will allow the comparison 2012 vs 2013.

If you only have a string MonthYear field, Left(MonthYear,3) or Subfield(MonthYear,' ',1) can be your first dimension and Right(MonthYear,4) your second.

For the moving averages, you'll find a good doc here:

Calculating rolling n-period totals, averages or other aggregations

Hope it helps.

[#3]

I'm having trouble getting it to work.  I found that doc earlier, and it works great with only one year's worth of data, but mine is over three years.  I changed the code to look like this:

sum(aggr(rangesum(above(total count({<Year=>}CustomerID),1,1)),Year))

It definitely gave me the total line with "no accumulation" selected, I need the average per month.  Changing the "sum" to "avg" doesn't do anything.

I tried to take care of it during the load:

Load

      `Date Received`,
      Count(`Date Received`) as var2012Average
       Where year(right(left(`Date Received`,10),4)) = '2012'

     Group By `Date Received`
;
SQL SELECT ...

But that eliminates all other possibilities other than 2012 and seems to eliminate any other manipulation of my 'Date Received' in the select statement.