Pie Chart breaks when values are zero

Gajapathi R M
Gajapathi R M ✭
inFormulas and Functions

In a dashboard, pie charts are breaks the reference and shows as Unable to display chart when reference values are changed to zero as these vales calculated via formula. Only Bar charts are showing as No data to display. My end goal is to show asNo data to displaywhen values are zero. Kindly help me out here.

Only Bar Chart.PNG


Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    So is it a sheet that has the calculations on it or a row report? If it is a sheet then you can add 0.00001 to all calculations and decrease the shown decimals. This will maintain some data greater than zero but only display zero.

    thinkspi.com

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Try: =SUM([Amount of fees Waived]:[Amount of fees Waived]) + SUM([Amount Written off]:[Amount Written off]) + SUM([Attorney fees]:[Attorney fees])<\/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":106336,"type":"question","name":"True count of Children(), not just non blanks","excerpt":"Doing project roll up and trying to use the formula below to roll up a complete status. =IF(COUNTIF(CHILDREN(), \"Complete\") = COUNT(CHILDREN()), \"Complete\", \"Not\") However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non…","categoryID":322,"dateInserted":"2023-06-12T20:08:17+00:00","dateUpdated":"2023-06-12T20:09:57+00:00","dateLastComment":"2023-06-12T22:39:12+00:00","insertUserID":161938,"insertUser":{"userID":161938,"name":"vanZylin","url":"https:\/\/community.smartsheet.com\/profile\/vanZylin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DWO5546FHG2S\/n63YWEB75TVCL.jpg","dateLastActive":"2023-06-12T21:09:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":161938,"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-13T00:08:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3373211849,"url":"https:\/\/community.smartsheet.com\/discussion\/106336\/true-count-of-children-not-just-non-blanks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106336\/true-count-of-children-not-just-non-blanks","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106336,"commentID":380221,"name":"Re: True count of Children(), not just non blanks","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380221#Comment_380221","dateInserted":"2023-06-12T22:39:12+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-13T00:08:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-12T20:39:58+00:00","dateAnswered":"2023-06-12T20:12:44+00:00","acceptedAnswers":[{"commentID":380185,"body":"

Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.<\/p>


<\/p>

Change<\/p>

COUNT(CHILDREN())<\/p>

to<\/p>

COUNTIFS(CHILDREN(), OR(@cell <> \"//www.santa-greenland.com/community/discussion/106206/\", @cell = \"//www.santa-greenland.com/community/discussion/106206/\"))<\/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":106335,"type":"question","name":"Match Learner by Location in a Training Tracker for Metric Sheet","excerpt":"I am missing something, but I think it is because I am not accustom to working with dates. I am creating a metric sheet for dashboard. I have a training tracker that populates a date by a learners name & demographics (row) with columns of different courses (date columns). I didn't think the formula would be difficult , but…","categoryID":322,"dateInserted":"2023-06-12T19:39:08+00:00","dateUpdated":null,"dateLastComment":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":150616,"lastUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3373200148,"url":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106335\/match-learner-by-location-in-a-training-tracker-for-metric-sheet","format":"Rich","lastPost":{"discussionID":106335,"commentID":380182,"name":"Re: Match Learner by Location in a Training Tracker for Metric Sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380182#Comment_380182","dateInserted":"2023-06-12T20:03:20+00:00","insertUserID":150616,"insertUser":{"userID":150616,"name":"benlight","url":"https:\/\/community.smartsheet.com\/profile\/benlight","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-12T20:23:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-12T20:23:36+00:00","dateAnswered":"2023-06-12T19:52:03+00:00","acceptedAnswers":[{"commentID":380176,"body":"

=COUNTIFS({Learner Location Range}, [Location]@row, {Course Completion Date}, ISDATE(@cell))<\/p>

If I understand your scenario correctly, this may work.<\/p>"},{"commentID":380182,"body":"

Awesome, <\/p>

Exactly it! I was not identifying the date@cell. <\/p>

Thank you so very much!<\/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":[]}],"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":9974,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • SUMIF across three rows

    Hello everyone, I'm attempting to create a formula that will add all three rows together. I've tried: =SUMIF([Amount Written off]:[AmountWritten off], [Amount of feesWaived]:[Amount of fees Waived], [Attorney fees]:[Attorney fees]) I've also tried =SUM([Amount of fees Waived]:[Amount of fees Waived],+=SUM([Amount Written…

  • True count of Children(), not just non blanks

    Doing project roll up and trying to use the formula below to roll up a complete status. =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "Not") However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non…

  • Match Learner by Location in a Training Tracker for Metric Sheet

    I am missing something, but I think it is because I am not accustom to working with dates. I am creating a metric sheet for dashboard. I have a training tracker that populates a date by a learners name & demographics (row) with columns of different courses (date columns). I didn't think the formula would be difficult , but…