-
1. Re: how to show blank cells when sum is 0
Matt LuttonNov 18, 2013 7:34 AM (in response to Tara Carter)
You could replace the original field with:
IF sum(Field)==0 then '' // IF THE SUM IS ZERO, REPLACE WITH AN EMPTY STRING
else str(sum(Field)) end // OTHERWISE, RETURN THE SUM AS A STRING
But the result will be a String, not a number, which may lead to other headaches and I'm sure there are other ways to accomplish this.
-
2. Re: Re: how to show blank cells when sum is 0
Shawn Wallwork Nov 19, 2013 6:49 AM (in response to Matt Lutton)Overly complicated, inferior solution deleted by author.
--Shawn
-
3. Re: how to show blank cells when sum is 0
Jonathan DrummeyNov 19, 2013 7:59 AM (in response to Shawn Wallwork)
I might be over-simplifying here, but what about something like:
IF SUM([Sales]) > 0 OR SUM([Sales]) < 0 THEN SUM([Sales]) END
?
-
4. Re: how to show blank cells when sum is 0
Matt LuttonNov 19, 2013 6:35 AM (in response to Jonathan Drummey)
More likely that I was over complicating it, I'd think.
-
5. Re: how to show blank cells when sum is 0
kettan Nov 19, 2013 7:58 AM (in response to Tara Carter)1 of 1 people found this helpfulI think custom number formatting is what you are after.
In Excel it is usually enough to write 0; -0; where the last semicolon formats zero as blank, but it doesn't work like this in Tableau. However, it works if you write "" indicating a text: 0;-0;""
Ps. Online Help says custom format can be specified by an Excel style number code ... and it seems to work almost like Excel. A more detailed documentation of which code can be used would be helpful and therefore hope some Tableau technical writer employee reads this.
-
6. Re: how to show blank cells when sum is 0
Shawn Wallwork Nov 19, 2013 6:47 AM (in response to Matt Lutton)Matthew you think you over-complicated it? I REALLY over-complicated it! With pictures and everything. Oh well.
Cheers,
--Shawn
-
7. Re: Re: how to show blank cells when sum is 0
Shawn Wallwork Nov 19, 2013 6:56 AM (in response to Jonathan Drummey)Even simpler: IF SUM() <> 0 THEN SUM() END
-
8. Re: how to show blank cells when sum is 0
Jonathan DrummeyNov 19, 2013 7:58 AM (in response to kettan)
Thanks for the tip, Johan, I always forget what's possible with the custom formatting!
With regards to your documentation note, here's a post from a couple years ago with a bunch of what we can do:
http://www.clearlyandsimply.com/clearly_and_simply/2011/04/tableau-quick-tip-2-custom-number-formats.html#more
-
9. Re: how to show blank cells when sum is 0
Jonathan DrummeyNov 19, 2013 8:00 AM (in response to Shawn Wallwork)
Shawn, did you reply by email? I had used SUM([Sales]) in my original reply but it didn't make it through to the post, I've sent an email to Dustin & Tracy to get their feedback.
-
10. Re: how to show blank cells when sum is 0
Tara Carter Nov 19, 2013 8:01 AM (in response to kettan)Thanks Kettan! This works!
-
11. Re: how to show blank cells when sum is 0
kettan Nov 19, 2013 8:03 AM (in response to Jonathan Drummey)Thanks for the link. It is very helpful.
Interesting findings in regard to this question about blank zeros:
{0;-0;} shows zero
{0;-0; } hides zero
The only difference is that a space is added after semicolon.
-
12. Re: how to show blank cells when sum is 0
Shawn Wallwork Nov 19, 2013 8:19 AM (in response to Jonathan Drummey)Nope, I just posted on the forums, but did copy your format.
From: Jonathan Drummey
Sent: Tuesday, November 19, 2013 9:00 AM
To: Shawn Wallwork
Subject: Re: - how to show blank cells when sum is 0
Tableau Support Community
how to show blank cells when sum is 0
reply from Jonathan Drummey in Forums - View the full discussion
NOTE: This was sent as a reply to your email notice.
-
13. Re: how to show blank cells when sum is 0
Shawn Wallwork Nov 19, 2013 8:18 AM (in response to kettan)That's just weird.