How to hide 0 In Tableau
Tableau in Two Minutes - Dynamically Hiding NULLs in Table Calculations
0:00 / 0:00
Quality:
Sharing buttons:
hi folks welcome to another episode of
tableau in two minutes today we're going
to be showing you a method for
dynamically hiding different parts of
the table calculation when you don't
want them to show up on your dashboard
so let me show you what I mean
first of all we're going to connect to
our superstore data set I'm gonna use
the orders and then we're just gonna go
ahead and create a simple line graph of
year-over-year growth of sales so we're
gonna take order date expand it out to
the month level and then we're gonna
take our sales drop them onto the rows
shelf here oh my goodness I can't hit it
there we go
drop that onto the rows shelf and then
set this up to be year-over-year growth
now you'll see that obviously for the
first year since we don't have anything
to compare to we have null values in
there and this is a little bit ugly we
wouldn't necessarily want to add this to
a dashboard in this particular state
without making sure that we hide that
now one way to do that is to just hide
2014 but of course then if our data
changes and we have a different starting
year then 2014 will stay hidden look for
example 2015 would show up that's not
particularly helpful the easiest way to
do this is to click on the sum of sales
hit filter and you don't actually need
to make any changes here but you want to
make sure that included null values is
unchecked and then once you apply this
filter you'll see that 2014 is hidden
but we do have the values for rum for
2015 16 and 17 this will change so if we
added a filter and for example we
removed 14 and 15 then we would just
have 2017 because 2016 is our first year
that will all be null and then we're
calculating a year-over-year growth into
2017 now what's helpful is understanding
how to do this yourself because again
that gives you a little bit more
flexibility so we're going to come up
with a slightly different scenario the
first thing we're gonna do is we're
gonna drag sales out here we're gonna
take our order date and we're just gonna
look at the last two years so we're just
going to look at 16 and 17 and then
we're gonna turn this summit sales into
a table calculation with your over year
growth and then I'm going to drag sales
out
next to it so now we have two different
things right we have one that has a
value for 2016 one that doesn't have a
value for 2016 now we could use the same
technique in this particular case to
hide the null values and that would hide
2016 but there are situations where
that's not necessarily going to work so
it helps to have a way of only keeping
the most recent year that's the one with
complete values while excluding anything
or hiding rather anything that may have
values because we can't exclude them
because then 2017 will be the first year
we wouldn't have any values there so we
want to hide not filter these values to
do that we're going to create a
calculated field that we're gonna call
show/hide and it's just gonna be an if
statement what we want to say is if the
year of order date is the same as the
maximum year of order date then we want
to hide it otherwise I'm sorry then we
want to show it
otherwise we want to hide it now
obviously this is not gonna work it's
not gonna work because I'm actually
comparing a row level calculation to a
aggregate calculation and the way we
turn aggregate calculations into row
calculations is by using a level of
detail calculation so now this is
comparing the year of the order date to
the maximum year of the order date
across the whole data set is saying that
if this year is the same as the maximum
year and all of our data set then we
want to show it otherwise we want to
hide it so let's go ahead and apply this
and then let's drag this out above the
year I'm gonna have to change something
because this will obviously impact the
way the table calculation works so we
just want to make sure it's also
included in the table calculation so we
continue to get this nice neat nice neat
set here and now you can see that
because we have a show on this one and a
hide on this one we can hide 2016
without actually hiding 2016 what we're
doing is we're hiding the hide column
which is the first year of our data and
we're keeping the show column which is
the most recent year of our data that
will obviously dynamically calculate
depending on what those two years are so
this may not be 2017 it may be 2018 as
we roll into next year maybe 2000 I'm
sorry 2018 as we add the new years data
to this data set or maybe 2019 as we
roll into next year now the one thing
that's left obviously is that we have
headers on here which are a bit ugly so
we can go ahead and just uncheck show
headers and then here we have a
visualization that looks basically the
same as it would if we just had 2017 in
there but we've hidden a prior data that
we're using to base using as a base for
our table calculation so that's how you
could hide something dynamically if you
needed to do that without using just
filtering for nulls in the and the sum
of sales table calculation that about
does it for this episode of tableau in
two minutes if you have any questions
then please leave them in the comments
if you like what you hear then please
subscribe to our channel and we will see
you next time
