Charts Plotting 0

Tama WagnerTama Wagner ✭✭✭✭
edited 12/09/19 in Using Smartsheet
11/18/19 Edited 12/09/19

I recently contacted support to find out if there was an option within dashboard charts to keep the charts from plotting 0. I received the following response:

Currently we don't have a feature in place that will prevent specific values from showing on the chart. At this time the only solutions that we can provide would be to remove the zeros from the cells, which would prevent our system from detecting data and plotting data points, or modify the chart data to not include the future months. 

Unfortunately, I do not have the ability to remove the 0's since my data comes from calculations, or modify the chart to not show future months, since we want to see future months compared to past year data.

I would like to vote to have the option to turn off values to be plotted in a chart, such as 0, so that my charts appear "cleaner". 

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Tama,

    Great idea! That would be a great addition to Smartsheet features.

    Please submit an Enhancement Request when you have a moment.

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I "hide" zeros in calculations quite frequently. Here is a basic example of how.

     

    Original formula:

     

    =COUNTIFS({Sheet A Range 1}, "In Progress")

     

    Gives me a count of how many "In Progress" statuses I have, but I want that cell to be blank if the count is zero or in other words: populate the number if it is greater than zero.

    So I use an IF statement to accomplish this.

    .

    =IF(COUNTIFS({Sheet A Range 1}, "In Progress") > 0, COUNTIFS({Sheet A Range 1}, "In Progress"))

    .

    It basically says "if original formula is greater than zero, run original formula. Leaving the "else" portion of the IF off means it will default to blank.

     

    I use it A LOT in my reporting metrics that feed dashboards.

    thinkspi.com

  • Tama WagnerTama Wagner ✭✭✭✭

    Thanks! I will give that a try. 

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Hi @Paul Newcome - Your suggestion above for hiding "0s" using formulas works well. However, the charts still show the labels for which the values are blank. Is there a way to hide the labels from the axis as well?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Sameer Khanna

    Insert another column and use a formula such as

    =IF([Column With Count]@row <> "", [Column With Label]@row)


    Then you can reference this column in your chart.

    thinkspi.com

  • Thanks @Paul Newcome. This helps. Sorry, somehow missed this message earlier.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • edited 03/28/21

    @Paul Newcome what if the data is imported from an Excel sheet formula? We import excel data with formula's. In excel we can hide zero values, but when we import into Smartsheet the 0 does show. How can we remove them? We use the imported data to generate PDF documents, and now all the 0's show on the documents.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Mathijs Muller You would want to insert a helper column and use the formula outlined in my comment above where you say IF the value is greater than zero then display the value.


    thinkspi.com

  • @Paul Newcome Hi Paul, thanks for that. However, still not clear to me where I add the formula? I import data from excel, which creates a new sheet. From the new sheet, I copy the row to my existing sheet. In this sheet where I copy to, the "zero" should be hidden/ taken out. Can I put the formula to cover the complete sheet?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    You would insert the new column in the existing sheet. and put the formula into every row.

    thinkspi.com

  • @Paul Newcome Hi Paul. I've used the formula above but where the cell count was 7, on executing the formula the cell returns 0, what am I missing?

    =IF(COUNTIFS({Design - Request Register Range 3}, ([email protected]), {Request Tracker Range 2 - Status}, "In Progress") > 0, COUNTIFS({Design - Request Register Range 3}, "In Progress"))

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Jason P Both COUNTIFS should be identical.

    thinkspi.com

Sign In or Register to comment.