Reference lines for charts

[#1]

Hi all. I have a year over year line chart showing data for the past 8 years (see attached: x axis shows each month; every line in the chart shows values for a different year). I'd like to be able to add in a reference line that shows the average for each month (ideally across the past 5 years, but across all if necessary). I'm not sure how to go about doing it. I've tried using the reference line feature in the presentation tab of the properties box, but I can't figure out how to include the dimension into the equation. I've also tried using an average trendline, but that just gives me the average line for each year instead of an average value for each month (see second image). Any help would be much appreciated!

[#2]

Whoops. Forgot to add the second graphic and the forum is giving me trouble when I try to upload it. If you're curious about what I mean, let me know and I'll try to find some other means of showing it.

[#3]

Say your expression is sum(sales) in your reference lines add the expression:

avg(aggr(sum(sales),Year,Month)).

Kiran.

[#4]

Tried it without luck. Why do you include year and month as aggr parameters?

[#5]

Because you want to take avg of values at month and year level. Try creating a variable with the above expression and add the variable in your reference lines. If the problem persists post your application for us to debug.

Kirna.

[Anonymous]

Have you already tried with the Trendlines options of the expressions??

[#7]

Yup. It gives me an average trendline for every single year. I want a single trendline that shows the average for each month.

[#8]

Kiran, finally got your suggestion to work, but it only returns a single value. I want a different value for each month. I've been doing a little bit of research on the aggr function and it seems like I should want something closer to

aggr(avg(value_name),month). The only problem is, that doesn't work. Any thoughts?

[#9]

I think the fundamental question is: can you create a reference line that depends on the dimension(s) or must all reference lines be inherently flat?

[#10]

All reference lines are inherently flat. Reference lines are something like target lines where you would want your values be at. Coming to different values at different lines; I need to understand more here. If converting your values into a straight table as below what would be your reference logic?

Year, Month, Value, Reference Value

2011,Jan,100,?

2011,Feb,100,? etc...

I am expecting an answer in the lines of avg(Jan,feb..dec) for each year or avg(Jan) for all Years etc...

If you can fill in this, I can suggest you a way it is to be achieved.

Hope my question is clear.

Kiran.