2 Replies Latest reply on Nov 8, 2016 8:11 AM by michelle.smith.2

    Dynamically Sort Trend Graphs using a Look back Period

    michelle.smith.2

      i'm hoping someone can help me.  I need to show a line graph of data over a year, where each month's data point represents a rolling 12 month sum, inclusive.  So, in my attached example, the data point for Jan 2013 represents the sum of sales from orders placed between feb 1 2012 - Jan 31st 2013.  For the December point, it's a sum of sales from 1/1/13 - 12/31/13.  I believe I have that part working correct with a Lookup function to look back at the previous month sales while only showing the year I'm interested in (worksheet trend graph).

       

      The next part I need to do is to sort the trend graphs by descending sales amount based on the most recent 12 month look back (so in the example the 12/2013 data point) and only show the top 5 subcategories.  So, in the worksheet 'Text Table', I have the sub categories ranked by descending sale amount for the most recent look back period and am only showing the top five using a filter.  this seems to also update correctly when I change the Region filter.  

       

      Somehow I need to combine the correct sorting and filtering of the Text Table and apply it to the trend graph, which I can't seem to figure out.  I am only showing the trend graph on the dashboard, not the text table.  Is there a way to sort the trend graphs by descending order of the most current look back period and only show the top 5 and have that continue to apply if the user changes a filter?

       

      Thanks so much!

      Michelle