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!
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.
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.
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?
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
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.