1 of 1 people found this helpful
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
Is there no way I can create an if statement or formula which says if that measure is 0 put a hyphen over there?
You can, and I just added an example of that, but the resulting values will be Strings, which can cause other problems for you.
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?
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.
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.
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
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.
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.
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.