0:00Hey it's Timmy, in today's video we're
0:01going to be looking at the ceiling and the
0:03floor function
0:04in Tableau. Essentially these round up and
0:06round down in Tableau, so let's get stuck
0:08in,
0:08let me show you how they work. If I hit the
0:11superstore sales data source here,
0:13it's just to save data source, so whatever
0:15data source you've got access to, all we
0:17need to do is
0:17make sure we create a table which has some
0:20decimal places in it. So let's go ahead and
0:22bring sales in
0:23to get some decimal places, I'm actually
0:25going to make this an average and I'm going
0:27to put this
0:28back on label so we get an actual table and
0:30then we're going to break this down maybe
0:32using
0:33subcategories. So if I go get my product
0:35hierarchy here, drag subcategory down, you
0:37'll see that we
0:38get a bunch of values. Tableau does sort of
0:40look at the format and it decides to remove
0:42the decimal
0:43places, so I'll right click on this, go to
0:45format, then go to field, then select
0:48average sales and
0:49what I'll do is I'll do number custom and I
0:51'll bump it right up so we've got lots of
0:54decimal
0:54places available to us. What I will also do
0:57is I'll just go to the formatting option,
0:59so let me just go to the font here and what
1:02I will do is I'll make the font a little
1:04bit larger
1:05so that you can see that more clearly. So
1:07here we can see everything's working
1:09perfectly fine
1:10and because we're taking averages it ends
1:12up sort of resulting in some decimal places
1:14.
1:14So let's take a look at the first one which
1:16is the ceiling function. The best way to
1:18think of this is
1:19a room. To reach the ceiling you have to go
1:21up and to reach the floor you have to go
1:23down, so that's
1:24exactly what the functions do. Ceiling
1:27rounds up and floor rounds down essentially
1:30. So let's go
1:31ahead and let's just edit this field in
1:33here inside of the calculation window. So
1:36all I'm
1:37going to do, I'm going to hit a space,
1:39there's a little bit of a weird behavior
1:40where if you just
1:41start typing the function right next to a
1:43previous function it sort of deletes the
1:44function that you
1:45were just sort of following. So let's just
1:48type in floor here, you can see that it
1:50auto completes
1:51and it gives us two brackets. So what we're
1:53going to do, we're going to grab this and
1:55we're going to
1:55put this here and what I will do is I will
1:57bring the average sales back in as well so
1:59we can see
2:00this. So what I'll do is I'll actually copy
2:02this in advance and now that we've written
2:04that floor
2:04around the average sales hit enter you'll
2:07see that you get these values. Now to sort
2:09of see what that
2:10actually looks like versus the original set
2:12of values let's just hit paste on average
2:15sales,
2:15you'll see that it goes there but it doesn
2:17't come into our table. I can now drag it
2:19into our table
2:19and we get the decimal places back. So let
2:21's have a look and see what this is doing.
2:23So essentially
2:24this should be rounding down under every
2:27single circumstance okay so 215.9 should go
2:30down to 215.
2:32Essentially it's going to the nearest whole
2:34number yeah so 230.7 is going down, 34 it's
2:37going down,
2:38133 is going down. So essentially these
2:41numbers on the right should always equal
2:43what's here on
2:44the left essentially. There shouldn't be
2:46anything that's sort of going up or down
2:47and that's exactly
2:48what's happened. Now what I'm going to do
2:50is I'm just going to double click inside of
2:51this floor
2:52function that we've written here. I'm going
2:54to copy it again and I'm going to double
2:55click below
2:56to get to the new function. Then I'm going
2:58to paste this and instead of floor I'm just
2:59going
2:59to type ceiling okay so ceiling. You'll see
3:02that it comes up there for auto completion
3:04and we just hit enter now that it's written
3:07in there hit enter and now you can see that
3:09on the
3:10ceiling function it's doing the opposite
3:12thing. And so you can sort of see which one
3:14is which if
3:14you just look at the top here you can see
3:16that the functions are sort of described
3:17there. That's
3:18sort of why I wrote them in this little
3:20calculation window on the left hand side
3:22because it doesn't create the calculations
3:25it just sort of does the quick ad hoc
3:27calculations and then
3:28it names the columns accordingly. So you
3:30can actually see the columns here inside of
3:32this table
3:33at the top so you can see which one's doing
3:35what okay. So that's pretty much it there's
3:37not much
3:37more to these functions they're just really
3:39handy for rounding up and rounding down.
3:41Actually tableau
3:43if you tell it how many decimal places you
3:46want will sort of make the best job of what
3:49it can do.
3:49So in this particular case I'm actually
3:52looking at the wrong thing here if I go
3:54back and select
3:55just two decimal places you'll see that it
3:58looks like this. If I go down to one
4:00decimal place
4:00you'll see that it actually rounds 215.9 up
4:05because essentially the seven rounded up
4:08makes this round up to 216. And so that's
4:11that's basically how it works when you
4:13specify decimal
4:14places tableau is doing this automatically
4:17if it's over halfway or round up if it's
4:19under halfway it
4:20rounds down and so on and so forth
4:21depending on the number of decimal places
4:23that you're sort of
4:23going for it'll sort of try and make the
4:25best sense of what's going on okay. So that
4:28's pretty
4:28much how it works in a nutshell and
4:30hopefully you found this video useful if
4:33not please let me know
4:34what you'd like to see instead and check
4:35out some of the other videos on logical
4:37functions that I've
4:38got across my whole channel also visit
4:40tableautim.com to watch lots of other great
4:43content that I've
4:44got available it's also on youtube but I
4:46think it's better to browse it there and
4:47also join me
4:48on discord if you've got any questions I'm
4:50always there sort of answering questions
4:51from time to
4:52time so yeah check it out and I'll catch
4:53you in the next video.
Tableau | Data visualisation | Analytics