displaying charts that update data based on date

Tammy Luther

So here is my situation. I have been tracking COVID case counts from across the US where we have our locations. Each day I enter into a master sheet the count and then have additional sheets that calculate the number of new cases by the week (so we can see week over week changes) as well as a second sheet that calculates a 5 day rolling average. All of these have data back to 3/16/20. Then on the dashboard, I populate multiple graphs/charts that show a period of time (typically the last month) for our locations by greography. Example is in our NE of the US I will select data for the locations that fall into this area (there are 5) and then I move the West, Midwest - ... you get it I think. The issue I have is that I have to go in each week (or whenever I remember) to update the data in the chart so it stays current. I am trying to figure out a way that would allow the chart to update and display current date with a set timeframe (example would be when you open the dashboard today it would show back 1 month ... when you open the dashboard in 2 weeks, it would show 1 month back from that date). There is alot of time to go in and redo the charts each time and I'm looking for a better way to display either using the charts or reports or whatever may be available. As we look at trends charts are better than looking at the raw data.

Any help would be appreciated. I'd be happy tor provide some screenshots, but not sure what would be useful.

I thank you for any feedback.

Mark Cronk

Hi @Tammy Luther ,

Try building a sheet that contains the data you want in your chart. Make the data dynamic so that it continuously updates to the date ranges you want displayed. Your formulas will be external links to your base sheet. Happy to help create the formulas if you need assistance.

If your needs are simple enough you may be able to do this with a summary sheet report. You'd build the dynamic date formulas into your summary sheet fields. Then populate a summary sheet report that's used to create the chart.

Understand the concept? Help?

Mark

Tammy Luther

@Mark Cronk ,

Mark, I'm still new to creating formulas and see you offered to assist. I'd like to take you up on that. Would you be willing to do so so? my email is tluther@cyrusone.com thanks