Chart Live Update

Jacob Laan
Jacob Laan ✭
edited 12/09/19 inSmartsheet Basics

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

Tags:
· Share on Facebook Share on Twitter

Comments

  • Sasan
    Sasan Employee

    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

    · Share on Facebook Share on Twitter
  • Jacob Laan
    Jacob Laan ✭

    No problem, see below (Screenshot 5).

    不确定它是否相关,但埃尔se 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

    · Share on Facebook Share on Twitter
  • Sasan
    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

    · Share on Facebook Share on Twitter
  • Jacob Laan
    Jacob Laan ✭

    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.

    · Share on Facebook Share on Twitter
  • Sasan
    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

    · Share on Facebook Share on Twitter
  • Jacob Laan
    Jacob Laan ✭

    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.

    · Share on Facebook Share on Twitter
  • Sasan
    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

    · Share on Facebook Share on Twitter
  • Jacob Laan
    Jacob Laan ✭

    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

    · Share on Facebook Share on Twitter
  • Sasan
    Sasan Employee
    edited 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

    · Share on Facebook Share on Twitter
  • Jacob Laan
    Jacob Laan ✭

    Sasan,

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

    · Share on Facebook Share on Twitter
  • Sasan
    Sasan Employee

    Pleasure! And thank you for raising this so that we can fix for all other customers that would use@cellin cross-sheet formulas.

    Sasan

    · Share on Facebook Share on Twitter
  • Bar243
    Bar243 ✭

    Hi

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

    I am experiencing the same issue as above.

    Thank you

    · Share on Facebook Share on Twitter

Categories

@Shawn_K2<\/a> UGH I make a mistake. I copied your formula without looking at it close enough 😜<\/span><\/p>


<\/p>

try =COUNTIFS({CPR Request Type}, HAS(@cell, \"VAVE\"))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106847,"type":"question","name":"Missing drop-down options in my filter selections. Where did they go?","excerpt":"I have been creating filters and for some reason a couple of my drop-down options are not showing up as filter selections, even though they are correctly showing up as drop-down options in the sheet. To illustrate, here are the drop-down options I have for a column in my sheet, which are working correctly: but when I try…","categoryID":321,"dateInserted":"2023-06-23T18:16:31+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T16:54:06+00:00","insertUserID":162342,"insertUser":{"userID":162342,"name":"mgreenwalt","title":"coordinator","url":"https:\/\/community.smartsheet.com\/profile\/mgreenwalt","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T17:20:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T17:33:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":61,"score":null,"hot":3375348637,"url":"https:\/\/community.smartsheet.com\/discussion\/106847\/missing-drop-down-options-in-my-filter-selections-where-did-they-go","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106847\/missing-drop-down-options-in-my-filter-selections-where-did-they-go","format":"Rich","lastPost":{"discussionID":106847,"commentID":382360,"name":"Re: Missing drop-down options in my filter selections. Where did they go?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382360#Comment_382360","dateInserted":"2023-06-26T16:54:06+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T17:33:50+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\/2JV6OYUHWHZT\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:04:32+00:00","dateAnswered":"2023-06-26T15:48:56+00:00","acceptedAnswers":[{"commentID":382315,"body":"

Thanks @topazfae<\/a> for your time and efforts and I'm happy to announce that @Genevieve P.<\/a> solved this challenge 😃<\/span> - <\/p>

\"I can see that your two selections have 135 and 142 characters each. When I cut the character count under 100<\/strong>, they appear as options to filter by.\"<\/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":106837,"type":"question","name":"All columns aren't showing up for Grouping in Reports","excerpt":"I'm pretty new to Smartsheet and can't figure out what I am doing wrong here. I am trying to Group by the \"PM\" column but it's not showing up in the list. It's formatted as a dropdown, but other columns that are appearing on the list are formatted the same way. What am I doing wrong? Thanks!","categoryID":321,"dateInserted":"2023-06-23T15:59:37+00:00","dateUpdated":"2023-06-23T16:10:12+00:00","dateLastComment":"2023-06-26T11:46:03+00:00","insertUserID":161580,"insertUser":{"userID":161580,"name":"LDP","title":"Sr. Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/LDP","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-23T20:52:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":161580,"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-26T16:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":38,"score":null,"hot":3375317740,"url":"https:\/\/community.smartsheet.com\/discussion\/106837\/all-columns-arent-showing-up-for-grouping-in-reports","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106837\/all-columns-arent-showing-up-for-grouping-in-reports","format":"Rich","lastPost":{"discussionID":106837,"commentID":382247,"name":"Re: All columns aren't showing up for Grouping in Reports","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382247#Comment_382247","dateInserted":"2023-06-26T11:46:03+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-26T16:21:56+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\/2EJA60U6S04F\/pm-column-issue.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"PM Column Issue.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-23T17:47:52+00:00","dateAnswered":"2023-06-23T17:07:03+00:00","acceptedAnswers":[{"commentID":382035,"body":"

Is it set to allow for multiple selections? If so, reports cannot group by that. The only way to get it to work on a dropdown column is if it is set as a single select.<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=341&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":5408,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Using Smartsheet

  • How to Count a value in a drop down / multi-selection list?

    Hi, I'm wanting to count the total amount time this value "VAVE" is being assigned to a drop down column. I'm using a summary sheet to collect this data using formulas. So in the request type column(drop down list) I want the total in this sheet. I also want a total by the Brand. I keep getting an incorrect number, like 48…

  • Missing drop-down options in my filter selections. Where did they go?

    I have been creating filters and for some reason a couple of my drop-down options are not showing up as filter selections, even though they are correctly showing up as drop-down options in the sheet. To illustrate, here are the drop-down options I have for a column in my sheet, which are working correctly: but when I try…

  • All columns aren't showing up for Grouping in Reports

    I'm pretty new to Smartsheet and can't figure out what I am doing wrong here. I am trying to Group by the "PM" column but it's not showing up in the list. It's formatted as a dropdown, but other columns that are appearing on the list are formatted the same way. What am I doing wrong? Thanks!