Dashboard Chart "unable to display chart"

Good day,

I am using the pivot table to populate a chart on a dashboard from 3 columns on a sheet. Salesperson, month, contract value. that translates to a single line per sales person that I use to populate the chart.

It is designed to maintain a running total of what work the salesperson has requested to be booked versus completed booked. The chart throws the "unable to display chart" when there is no data in the pivot table to populate the chart due to all the work being completed. I know it should be as simple as creating a dummy entry, but I am not sure how to make that happen.

There are at least 50 salespeople whose data populates the pivot table which in turn populates the chart.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@TDobson

    You're correct - if the chart has no data to map then it will provide an error message. If you want to keep one point of data in there so that there's something to display, what about keeping a "helper row" in the main source sheet that's assigned to a fake person, such as "Sample Data"?

    You could have this row update with the current month (using theRecord a Date workflow), and always keep a certain value in the Contract Value cell so it appears on the chart.

    Cheers,

    Genevieve

  • TDobson
    TDobson ✭✭

    Thank you Genevieve,

    I have a lot of people to add a helper row to on the main sheet. The Pivot Sheet does not really work well with the helper rows in my experience so far.

    I have created a dummy quote through the quoting software that creates a 1.00 quote called "Formatting Only" and I am testing it with some success. That will mean basically adding a quote to each persons quoting software to maintain the chart in a "empty" position until the next quote is created.

    It is still a lot of additional work for me. It would be great if the chart could be set up like the report in that when it is empty it maintains its presence until a new entry is applied.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@TDobson

    Thank you for clarifying, and yes, I see what you mean. Please provide your feedback to our Product team on how empty charts displayby filling in this form here!

    Thanks,

    Genevieve

  • I second that - why not have a "text" field that is displayed if no data ... like "No Data" ??


    I use dashboards to show open items for all employees (by project) ...For convenience I use the same dashboard layout for all - i did not want to customize for each, and the projects they work on can change.

    if they have no items on a certain project it comes up with that error instead of something nice like "No Data"


    Please add this enhancement!

  • I have to add my voice here, this just makes my dashboards look bad and like I've made an error somewhere - allowing us to display a custom message in the event of an empty report would be a good solution.

  • Hollie Green
    Hollie Green ✭✭✭✭✭

    I noticed on a template I used it shows results are empty instead of the error message so there has to be a way to do it but I have not been able to find anything. How was this done in the template?


    image.png


  • TDobson
    TDobson ✭✭

    I am ok with Results Are Empty as long as when the results show up that they auto populate.

  • Kyle Chipman
    Kyle Chipman Overachievers

    Hi all - I submitted this as a wishlist item. It's a real buzzkill in dashboards and it would be great to see it reworked. Feel free to upvote if you have a moment.

    Reworked Empty Chart State to replace "Unable to display chart" in Dashboards — Smartsheet Community

Help Article Resources

想实践工作直接与公式Smartsheet?

Check out the公式手册模板!
=COUNT(DISTINCT([Employee]:[Employee]))<\/p>

Replace [Employee] if your column has a difference name. This will essentially generate a list of distinct entries and then count them.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":109307,"type":"question","name":"Show approval status based on # of approvals vs. denials","excerpt":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show \"Yes\". If there is 1 or more denials, then the Approved column would show \"No\". If the total of the columns don't equal to 6, then the Approved…","snippet":"I'm looking for a formula that will show \"Yes\", \"Hold\", or \"No\" based on # of approvals. There are 6 total approvals needed, if all 6 approve, then the Approved column would show…","categoryID":322,"dateInserted":"2023-08-22T21:20:12+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":96646,"lastUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3385482292,"url":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109307\/show-approval-status-based-on-of-approvals-vs-denials","format":"Rich","lastPost":{"discussionID":109307,"commentID":392022,"name":"Re: Show approval status based on # of approvals vs. denials","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392022#Comment_392022","dateInserted":"2023-08-22T22:04:40+00:00","insertUserID":96646,"insertUser":{"userID":96646,"name":"@SPark","url":"https:\/\/community.smartsheet.com\/profile\/%40SPark","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!co_Wh_2S-n4!ZAPZGbmgZrw!TRZYwfjvFdi","dateLastActive":"2023-08-22T22:57:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AJ5OBZXXQRAU\/2820-29-intake-smartsheet-com-2023-08-22-14-16-38.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"(20) Intake - Smartsheet.com 2023-08-22 14-16-38.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T22:04:29+00:00","dateAnswered":"2023-08-22T21:36:22+00:00","acceptedAnswers":[{"commentID":392011,"body":"

Something like this: =IF([Approval Count]@row = 6, \"Yes\", IF([Denied Count]@row >= 1, \"No\", \"Hold\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":109287,"type":"question","name":"Formula help","excerpt":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do not match. The formula I wrote is pulling back 'invalid' ... I am missing something and cannot figure out what!","snippet":"=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row > [STR Schematic End]@row, 1, 0)) Above formula meant to trigger a red flag if the WD & STR Schematic End dates do…","categoryID":322,"dateInserted":"2023-08-22T17:45:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157887,"lastUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":28,"score":null,"hot":3385456532,"url":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109287\/formula-help","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109287,"commentID":391947,"name":"Re: Formula help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391947#Comment_391947","dateInserted":"2023-08-22T18:30:00+00:00","insertUserID":157887,"insertUser":{"userID":157887,"name":"holli.nunn","url":"https:\/\/community.smartsheet.com\/profile\/holli.nunn","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T18:29:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/DLAV2H53BW86\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T18:29:34+00:00","dateAnswered":"2023-08-22T18:25:02+00:00","acceptedAnswers":[{"commentID":391943,"body":"

Give this a try:<\/p>

=IF([WD Schematic End]@row = \"n\/a\", 0, IF([WD Schematic End]@row <> [STR Schematic End]@row, 1, 0))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&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":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions