Charts - Changing Data Range (Modify Range)

ker9

Hi, I have a Dashboard with about 12 charts from one sheet.  It looks at five years of data.  In 6 months I'm going to want to change that five year period.  Is there any method I can employ to make that happen automatically or will I need to reselect the data on each chart for the new time period? 

I'll be adding the new year to the existing columns.  The oldest year data will remain but will need it to drop off the chart and pick up the latest year data (running 5 years).

Thanks.

Chris McKay

Hi ker9,

The approach will be determined by how granular you want to be. You could 1) have Smartsheet drop off data the day after it crosses the 5-year threshold, or 2) you could have it drop off when the month ends (e.g. anything from March 2013 will drop off when we hit April 2018).

Either way, you'll need to create an additional checkbox column. Let's call this new column Valid:

1. To calculate whether the data should be included the day it ticks past the 5-year mark, stick this formula in the Valid column:
   
   
   
   =IF(DATE(YEAR(Date1) + 5, MONTH(Date1), DAY(Date1)) < TODAY(), 0, 1)
   
    
2. To calculate whether the data should be included both once the date ticks past the 5-year mark and the month changes, stick this formula in the Valid column:
   
   
   
   =IF(AND(DATE(YEAR(Date1) + 5, MONTH(Date1), DAY(Date1)) < TODAY(), MONTH(TODAY()) > MONTH(Date1)), 1, 0)

Now simply create a report that references your data sheet, making sure to place a Valid is checked criteria in the What? area of the Report Builder and use that to populate your graphs.

Hoping this is what you're after.