Charts Plotting 0

Tama Wagner
Tama Wagner ✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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 EXPERT CONSULTANT & PARTNER

    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 Newcome
    Paul 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 Wagner
    Tama Wagner ✭✭✭✭✭

    Thanks! I will give that a try. 

  • Paul Newcome
    Paul 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 Newcome
    Paul 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 Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    thinkspi.com

  • Mathijs Muller
    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 Newcome
    Paul 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 Newcome
    Paul 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 Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jason P Both COUNTIFS should be identical.

    thinkspi.com