Chart Live Update

Jacob Laan · 03/28/18

Hello,

We are experiencing an issue with the chart functions within Smartsheet. We are currently using Smartsheet to collect and process help desk tickets. We have a form hosted on our intranet where users can submit help desk tickets. Those are collected and processed by our help desk team all in one sheet within Smartsheet (Screenshot 1). We then have a second sheet that provides some simple, summary statistics of those help desk tickets by month by status (Screenshot 2). Finally, we are experimenting with a 'dashboard' of sorts that just provides a visual summary of the information compiled in the second sheet (Screenshot 3). This final piece is where the problem lies. We have it set to update every 2 minutes, and it appears to be functioning properly in terms of rendering the data as expected. However, when anything changes on the original source sheet (Screenshot 1), all of the charts fail (Screenshot 4). The only way to fix the issue is to navigate to the second, summary sheet (Screenshot 2) to, in-effect, synchronize the middle-man. Any way to fix this that doesn't involve pointing the charts directly at the form data?

Screenshot 1.png

Screenshot 2.png

Screenshot 3.png

Screenshot 4.png

Sasan · 03/28/18

Jacob,

Thanks for bringing this to our attention and thanks for including screenshots. It looks like the issue is beyond Charts as the metric widgets are showing invalid messages. Would you be able to provide a screenshot of (screen2) with the underlying formula in let's say [Submitted]3 cell?

Thanks

Sasan

Jacob Laan · 03/28/18

No problem, see below (Screenshot 5).

Not sure if it's relevant or not, but something else I'm noticing is that if there are changes to the source sheet (Screenshot 1 above), when I navigate from the dashboard to the summary sheet, all the calculated cells show an error for a split second (Screenshot 6), as if the cells just haven't loaded yet, and then the errors go away and the data appears. So, it's almost like if there hasn't been some interaction with the summary sheet since a change to the source sheet has occurred, Smartsheet knows that the source changed, but it hasn't processed that change in the summary sheet, and the dashboard, which is once removed can't resolve the discrepancy until the summary sheet is refreshed....something like that.

Screenshot 5.png

Screenshot 6.png

Sasan · 03/28/18

Jacob,

Given past the split second and your summary sheet has #s, and a forced Dashboard refresh by you, do the metric and charts show data and populate?

Thanks

Sasan

Jacob Laan · 03/28/18

Sasan,

Yes, if I update the summary sheet first, and then the dashboard second, the dashboard charts render as expected, but not all users who have access to the dashboard would necessarily have access to the summary or source sheets, and we were hoping that the dashboard would reflect a real-time (or relatively recent) view of the underlying data source without the need to manually refresh the underlying data source every time a change occurs.

Sasan · 03/28/18

Jacob,

Not sure this is about manually having to refresh the underlying sheet. It's more about a coincidental timing. Your summary sheet updates automatically when new data appears in source sheet. Dashboard widgets when refreshed, pull data from your summary sheet. In this instance, again timing, when the widgets where looking up the data in your summary sheet, the summary sheet was getting updated with source sheet data and in that split second it had not calculated the numbers. Therefore, dashboard widgets did not have #s to display. I think the mid-air collision of summary sheet getting updated and dashboard looking up the summary sheet is the issue. I hope that helps.

Thanks

Sasan

Jacob Laan · 03/28/18

Sasan,

I'd be inclined to agree with you except that I can recreate the issue every time there is a change to the original data source, and each time, the only way to fix it is to navigate to the summary sheet and, in-effect, 'refresh' it. In addition, once a change has occurred at the underlying data source, and the charts fail to load in the dashboard, refreshing the dashboard itself doesn't resolve the issue either. That is to say, if it were a random issue, caused just by the incidental collision of the change and the refresh call, I would expect it to resolve itself upon a subsequent call.

Sasan · 03/28/18

Jacob,

Ok, good to know. We need to troubleshoot this and get back to you. It's isolated to the summary sheet and underlying formula. Would you be able to share a screenshot of the cross-sheet formula cell selector screen?

Thanks

Sasan

Jacob Laan · 03/28/18

Sure thing.

Wasn't sure which exactly you wanted so here is:

Cell reference for Month Total Column - Summary Sheet (Screenshot 7)
Cell reference for Month Status Column - Summary Sheet (Screenshot 8)
Cell reference for Status Metric - Dashboard (Screenshot 9)

Screenshot 7.png

Screenshot 8.png

Screenshot 9.png

Sasan · 03/28/18

Jacob,

Thanks. This is helpful. We are finding that the@cellreference might be the culprit and a bug in cross-sheet referencing. In the mean time, here is a work around:

1. Add a column to your source sheet called "Month" (You can choose a different name).

2. Use a MONTH formula to extract the month # from "Date Requested" column to the "Month" column.

3. In the summary sheet change your COUNTIF formula to reference the "Month" column, without using the@cell.

That should help the situation in the interim, till we fix the@cellbug within a cross-sheet formula.

This should fix the entire flow from source-->summary-->dashboard and update all automatically when new data is added in source.

Let us know if this interim solution works for you.

Thanks

Sasan

Jacob Laan · 03/29/18

Sasan,

Yep, that did the trick. Thanks for stepping through this with me, I really appreciate the great customer service!

Sasan · 03/29/18

Pleasure! And thank you for raising this so that we can fix for all other customers that would use@cell在cross-sheet公式。

Sasan

Bar243 · 06/04/19

Hi

Do you have a date when the@cellissue will be resolved?

I am experiencing the same issue as above.

Thank you

Chart Live Update

Comments

Categories