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.

ker9

Chris, thank you for your help.  My understanding of charts (in Dashboards) is that they can't be created from reports only from single sheets (at this time).  

My data is in year columns so the charts pick up 5 columns (2013-2017). I'm going to need to change that to 2014-2018 for the rolling 5 year charts.

I suppose I could copy and move the 2013 data to a new column and then overwrite what's there with the 2014-2017 data, but this sounds like it could easily mess up formulas.  (I would definitely save a copy of the sheet first.)

Chris McKay

Thanks ker9,

Yes you are correct about charts not supporting reports. Hope Smartsheet fix that soon.

If your sheet is broken down into dates in columns like that, then yes it will more than likely be a manual process.

Hope that you can somehow get it working!

Sasan

If this helps, Charts against Report data will be available in the next few weeks, around mid-April or sooner.  You can get your formulas and Reports ready and as soon as Charts against Reports becomes available you can create the charts you need.  

Thanks

Sasan

ker9

Hi Sasan, so I could have a report for each chart (12) and then change the columns selected in the report to change the 5 year period.  (This will be easier than changing the charts.)

Based on how the sheet is setup, with columns across for years, I don't think I can use a formula to automate what is in the report - someone please correct me if I'm wrong, would like to be wrong

Thank you!

Sasan

Ker9,

Yes, you are right.  Chris has a potential solution and needs the years listed in a single column "Year", so that your Report always looks at that "Year" column for years, based on a criteria for Valid is checked.  This would require you to change the sheet structure from each year as column header, to years in one single column "Year", with years populated in each respective underlying cell.

Thanks

Sasan

ker9

I'd have about 100 columns across if I rotated it so that is not the answer.  It will help to base the charts off reports.

Sasan

Yes, completely understood.

Sasan

ker9

I created reports for each data chart and updated the dashboard to use the reports for charts - works great!

PROBLEM:  I did Save As New on the entire folder (containing data sheet, dashboard and reports) but the dashboard charts retained the link/data from the original reports and not from the new copy even though I selected use Newly Created Sheets.  Makes it difficult to use a template to create new dashboards.

When I select the new report for the data chart in dashboard, it requires me to rebuild the chart completely.  Not helpful.

I've gotten everyone to hold off on building more client data until we had the ability to use reports but now that we have it we want to move forward.  Any idea when you might fix this bug?

Sasan

Ker9,

Are you creating a new report in the new folder and then selecting the new report as the new source of data for an existing chart.

Sasan

ker9

Any progress on this bug?  When using Save As New on a folder, the charts on the dashboard retain the data from the original reports in the folder you save from rather than picking up the new reports (they stay linked to the original report).

Sasan

Ker9,

Yes, we have a fix and currently testing. We are planning to release the fix in mid May.  Thanks for your patience.

Sasan

Sasan

All,

The Save As New bug for Charts referenced above, is now fixed with the recent release.  Thanks for your patience.  

Sasan