12 Replies Latest reply on Feb 23, 2017 8:06 AM by chakradhar kakani

    Can I replace 0 with a hyphen -

    Pankaj Chopra

      I have a measure in one of my visualizations where I would like to replace 0's with a hyphen - . How to get this done? Thanks

        • 1. Re: Can I replace 0 with a hyphen -
          Matt Lutton

          There are several ways you might be able to do this, but perhaps the easiest is to convert the measure to a dimension, then alias the 0 values to a hyphen.  I'm not sure if this will work in all scenarios or not (it definitely won't work if the field is a calculated field), but its worth a shot.  If that doesn't work, can you post a packaged sample workbook?


          Otherwise, a calculation similar to this may work (but the resulting values will be a String, so that may impact other aspects of your work)


          IF count([ID]) == 0 then "-"

          else str(count([ID])) end

          1 of 1 people found this helpful
          • 2. Re: Can I replace 0 with a hyphen -
            Pankaj Chopra

            Is there no way I can create an if statement or formula which says if that measure is 0 put a hyphen over there?

            • 3. Re: Can I replace 0 with a hyphen -
              Matt Lutton

              You can, and I just added an example of that, but the resulting values will be Strings, which can cause other problems for you.

              • 4. Re: Can I replace 0 with a hyphen -
                Pankaj Chopra

                Thanks Matthew. You are right. The string values are causing some problems for example some of my numbers are displayed in 3 decimal places and some in 2 not sure whats going on. I was able to display "-" though but places where there are values the numbers are displayed in weird format. Can I change the format somehow?

                • 5. Re: Can I replace 0 with a hyphen -
                  Matt Lutton


                  See if this helps you work that out--you can use string manipulation, etc. to do the formatting.

                  • 6. Re: Can I replace 0 with a hyphen -
                    Pankaj Chopra

                    Any graceful way of replacing 0 in a data with a hyphen without screwing up the numbers? When I use


                    if ( field) == 0 then "-" else field


                    it messes up my decimal values in my other numbers which are present in the data. For some numbers I see 2 decimal places for some like continuous. I want to keep my formatting intact like just 2 decimal places.

                    • 7. Re: Can I replace 0 with a hyphen -
                      Matt Lutton

                      It should be possible to do what you want, with a string value.  Post a sample workbook that demonstrates the issue you're having, and we can try to help.  The link I provided above shows an example of a Parameter that allows you to swap between $ and % values, and the resulting values are strings that are manipulated to display properly.  Without seeing your data, I'm not sure how to apply this but if you post a workbook, someone can likely help.  You can take a subset of your data, paste it into Excel, extract that into Tableau, and post a packaged workbook with what you've managed to accomplish so far, and the error you are seeing.  Thanks


                      As an example, at the link I provided, if you chose "Ratio" from the "Choose Measure" parameter, the corresponding calculation to format it as a String with two decimal places and a percent sign following is something like:


                      str(round(sum([Profit])/sum([Sales])*100, 2)) + "%"


                      so something like str(round([Your Measure], 2)) should be applicable.

                      • 8. Re: Can I replace 0 with a hyphen -

                        If the issue is formatting only, you could use custom format such as  0;-0;"-"

                        140214 Can I replace 0 with a hyphen.png

                        3 of 3 people found this helpful
                        • 9. Re: Can I replace 0 with a hyphen -
                          Matt Lutton

                          so does this work to display any zero values from a measure as a hyphen, while retaining an integer/float/whatever data type?  How does it impact calculations, etc?  I haven't used this option much, but I know it has some useful applications.  Thanks for posting!


                          Nevermind, it looks like the measure will retain the data type, but an average on the values will consider the hyphen to be a zero value (which is probably why Johan asked if formatting was the ONLY issue here).


                          So, as always, the method used will depend on what you want to do with the resulting values.


                          Something like this may work for you:


                          IF sum(Value)=0 then "-" else str(round(sum(Value), 2)) end

                          • 10. Re: Can I replace 0 with a hyphen -

                            It doesn't change the datatype (as stated by you) and shouldn't therefore influence the calculations in any way.


                            An average of 0 should (in theory) also be displayed as "-". I am afraid 0 is not 0, but something like 0.00000000001. If so, creating a calculated field for the AVG which rounds the number to n decimals might solve the issue ... I think  :-)


                            I agree it is impossible to say if this solves the issue without knowing more about the visualization itself.

                            • 11. Re: Can I replace 0 with a hyphen -
                              Pankaj Chopra

                              Thanks a lot guys. Give me some time to try the above options and I will get with you and also post a sample workbook.

                              • 12. Re: Can I replace 0 with a hyphen -
                                chakradhar kakani

                                kettan I was looking for something like this..to replace zero with a symbol and is this possible to to have the number formatting along with thousand separators for the number? appreciate the help.