Chart Live Update
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?
Comments
-
Sasan EmployeeJacob,
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
-
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.
-
Sasan Employee
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
-
Sasan,
是的,如果我先更新汇总表,然后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 Employee
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
-
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 Employee
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
-
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)
-
Sasan Employee
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
-
Sasan,
Yep, that did the trick. Thanks for stepping through this with me, I really appreciate the great customer service!
Categories
=IF([Date as Text Type]@row <> \"//m.santa-greenland.com/community/discussion/comment/\", DATE(VALUE(LEFT([Date as Text Type]@row, 4)), VALUE(MID([Date as Text Type]@row, 6, 2)), VALUE(MID([Date as Text Type]@row, 9, 2))), \"//m.santa-greenland.com/community/discussion/comment/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107081,"type":"question","name":"Users having issues with accessing published sheets","excerpt":"HI I have had several users reach out to me indicating they are no longer able to access a published sheet. They have been able to access in the past but now they are not. Nothing has changed with these published. Yesterday for one user, I deleted their account and re-added. Once he accepted the invitation, he was able to…","snippet":"HI I have had several users reach out to me indicating they are no longer able to access a published sheet. They have been able to access in the past but now they are not. Nothing…","categoryID":321,"dateInserted":"2023-06-29T14:13:17+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T14:47:23+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-29T14:43:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T19:12:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3376099840,"url":"https:\/\/community.smartsheet.com\/discussion\/107081\/users-having-issues-with-accessing-published-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107081\/users-having-issues-with-accessing-published-sheets","format":"Rich","tagIDs":[219,223],"lastPost":{"discussionID":107081,"commentID":383099,"name":"Re: Users having issues with accessing published sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383099#Comment_383099","dateInserted":"2023-06-29T14:47:23+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-29T19:12:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/BO5O2K273I0W\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-29T14:45:56+00:00","dateAnswered":"2023-06-29T14:43:11+00:00","acceptedAnswers":[{"commentID":383094,"body":"
Does your company use Single Sing-On? There were some updates that took affect yesterday. I wonder if maybe that is affecting things since it seems to be an issue with being part of the company's account.<\/p>
<\/p>
