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

# Dynamically Sort Trend Graphs using a Look back Period

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

• ###### 1. Re: Dynamically Sort Trend Graphs using a Look back Period

Hi Michelle,

Find my approach as reference below and stored in attached workbook version 9.3

A. Offset: DATEDIFF('month',[Order Date (Exact)],[Order Date (Exact) Parameter])

B. For each Month: if [Offset]>=X and [Offset]<=X+11 then [Sales] END

C. Top X based on CM

D. Sub-Category sort on

Regards,

Norbert

• ###### 2. Re: Dynamically Sort Trend Graphs using a Look back Period

Thanks so much for your reply.  this solution gets close.  However, I need the x axis to represent the month of the preceding 12 month period.