Updating Dashboard

Grant

So I have created a Dashboard with Metrics and Graphs only to learn that as the underlying data in the sheet is updated the widgets do not update they are static. Given the number of dashboards to be built it is not feasible to manually re-point the widgets to the latest data. So I need a workaround. Given that the widgets are always looking at the same cells the question now becomes how to update those specific cells with the right data.

When the web form inserts data the widget does not look at the new rows it looks at those orginally selected.

Grant

In the underlying Data sheet rows are added at the bottom in order to get the graphs to report properly. If the data is organized most recent on top then the graph function puts data from most recent on the left to oldest on the right and people aren't used to reading data progressing through time from right to left. So it must update from the bottom for that reason as well as the fact that I believe that part of the solution may be to have a section at the top that is static to feed the dashboard.

So I am thinking the solution is to have a section of twelve rows at the top of the sheet that read the latest twelve months of entries. The question; is there a formula in smartheets to copy, link or fetch the data in another part of the sheet based on the latest date or last entry?

Chris McKay

Hi Grant,

You should be creating a Report to generate the necessary data and then point your Chart widgets in that direction.

This will allow you to set criteria that dictates what data is included in the Report (e.g. Column 123 is not blank) ensuring that your Chart is always picking up the whole dataset including new form generated rows.

It also allows you to sort your data (e.g. by month).

Kind regards,

Chris McKay

Down Under Smartsheet Support

Grant

Ok that worked. Thank you so much. 

Now what about the metric widget? It does not seem to have the option of pointing toward the Report?

Chris McKay

Hi Grant,

No, the Metrics widget does not work with Reports yet.

Is the underlying data populating your Metrics not refreshing either? How is this data being calculated?

Kind regards,

Chris McKay

Grant

I selected the last row in the sheet expecting the widget to fetch the most recent. When the sheet was updated with a form and a new row inserted the widget keeps referencing the original row selection.

Thanks Chris

Grant

Actually Chris I did it again with another Dashboard and it worked. I went back into the original sheet and noticed that a blank row had been inserted. I've seen this before with Form updating of the sheet. So I'm now guessing that the widget does work and pull the latest row but that the form update inserted a blank and messed it up. In your experience the Metric widget does grab the most recent row right?

Chris McKay

Hi Grant,

Yes, blank rows are a real hassle.

The Metric widget will use refreshed data automatically and used in combination with Reports and Charts can create some great dynamic results.

The Metric widget you are referring to, were you expecting it to automatically pull the value in the most recent row? If so, it's not designed to do that out of the box. Metric widgets are useful when you want to check the most recent value of a static cell. You'd then ensure that the static cell is pulling the updated data you require.

Kind regards,

Chris McKay

Grant

Chris have you seen a formula that can do a Vlookup based on date? Meaning if a sheet is being filled in by period at the bottom row then the top row can be used to search for the most recent entry and return that to a cell the metric widget is looking at to update.

It would have to use a combination of Vlookup and today functions to retrieve the latest data as it is entered.

Grant

Problem solved. The following formula in the top row is referenced by the widget and will return the latest KPI.