2 Replies Latest reply on Apr 18, 2018 11:23 AM by Eric Viglotti

    Monthly average followed by individual months on chart

    Eric Viglotti



      I have 10 years of data and I'd like to show both historical trends and recent monthly trends in one chart. Anyone know a slick way of making it so that the first 9 years just show as 9 points calculated as a "monthly average" for each of those 9 years and then the last 12 months shown as the actual months? Meaning that it would be a line chart with 9 + 12 = 21 individual points on a line graph.


      So the x-axis would read something like:


      2008 Mo Avg

      2009 Mo Avg

      2010 Mo Avg

      2011 Mo Avg

      2012 Mo Avg

      2013 Mo Avg

      2014 Mo Avg

      2015 Mo Avg

      2016 Mo Avg















        • 1. Re: Monthly average followed by individual months on chart
          Mark Holtz

          As a line chart, this seems like it'd just be confusing.

          Line charts are usually used to show a trend through a consistent basis of time.


          Here, you're actually switching the time unit on the user, and I can't even think of a good way to do that.


          Even if you were talking about a Table/Crosstab view instead of a line chart, which I don't think you are:

          Without making a calculated field for each of your columns and using Measure Names with Measure Values, I'm not sure you can show all of this on a single view.


          I think the easiest path to do what you're proposing is to create 2 views and put them on a dashboard next to each other.

          Would that work?

          I think you could still remove headers of dimensions and even the axis label (you might want to fix the y axis scales identically) to "make it look" like the 2nd was pretty much just a continuation of the first...


          Just food for thought:

          I think a more typical visualization with data like you're describing would be:

          Use YEAR (discrete) in the color shelf.

          Use MONTH (discrete) in the Columns Shelf

          Show the Measure as a line.


          You'd get n (n = the number of years shown, 10 in your case) overlapping lines on the same chart, and you could color the years differently.

          You could color all the prior years very pale and then color the most current year much more boldly to help it stand out.

          • 2. Re: Monthly average followed by individual months on chart
            Eric Viglotti



            Thank you for your thoughts on this. In the end, you're point of the two next to each other is brilliant as it gives basically the same impression I'm looking for, takes up virtually the same space, but still keeps them separate enough that the reader would notice that one is a monthly average by year and the other is the actual results from the recent months, and locking in the y-axis to make them match would definitely help.