0:00Hey, it's Tim here. In today's video I'm
0:01actually going to show you a couple of
0:03tricks to help you write calculations in a
0:04nice clear way and actually allow you to
0:07test them as you build them.
0:09So let's get stuck in. Okay, so I'm here in
0:11Tableau. What I'm going to do is I'm just
0:13going to open up Superstore sales here. I'm
0:15going to open the American one. This is the
0:16global one that typically most people have,
0:19but it doesn't matter what you're using.
0:20You should get the same results if you open
0:22up Superstore in Tableau.
0:24Now here's my data set. You can see that it
0:25's pretty much ready to go. What I'm
0:27actually going to do is build a very simple
0:28table so that everything is really nice and
0:30clear. I'm going to go to the product
0:32hierarchy here, grab category and sub
0:34category and put those two next to each
0:37other.
0:38And then I'm going to go and get the
0:39segment. This is the customer segmentation
0:40that's done in the data source. I'm going
0:42to put that over here. And we're just going
0:44to leave it at that. You can see that in
0:46the table you have this label that says ABC
0:50.
0:50That's just Tableau letting you know that
0:52it would ideally like you to put something
0:54on the text label here so it can display
0:56those values. If I go ahead and do that, if
0:58I put sales on text, you'll see that those
1:00ABCs disappear and I actually get some
1:02values.
1:03But for this video, I actually want to keep
1:04that clear because what I'm going to show
1:06you is going to show you essentially a
1:08better way of writing calculations. Right,
1:10let's get stuck into this little trick.
1:12So I'm going to go ahead and write a
1:13calculation. And what I like to do doesn't
1:15matter what calculation I'm writing, if I
1:18just bring it into view here, I've got a
1:20got another screen dialed in here. So
1:22everything's a little bit bigger than it
1:24should be.
1:25Let's just resize this down. It's actually
1:27massive. I don't know what is going on here
1:29. But let's bring this in. There's my little
1:31sidebar. Let's bring that in a little bit
1:33more and keep going until it's small enough
1:36for what we need here.
1:38And then I'll bring that back open so you
1:39can see everything that's going on my seat
1:41on screen. Now what I like to do with
1:43calculations, I like to spell them out
1:45before I start to write them. So tip number
1:47one, make sure you comment your
1:49calculations.
1:50Now there's two ways of doing comments in
1:51Tableau. If you just want to do a single
1:53line comment, you can just do double
1:54forward slash, and you're pretty much good
1:56to go. So comment here.
1:59And if you want to comment out a whole
2:01section, you could do a forward slash star,
2:03write whatever you want, go to the very end
2:06to do a star and a backslash. And that's
2:08going to do exactly the same thing. And
2:10anything you put in between the comments is
2:12basically going to be commented out.
2:14Now, of course, this calculation is not
2:16valid, because it's looking for something.
2:18So if I was just to go here and type the
2:20sum of cells, you'd see that this would now
2:22become a valid calculation. Actually, sales
2:25is not a field in itself. So let's make
2:27sure we actually use a value that exists.
2:30And you can see that's valid now. So that's
2:32essentially how to come in a calculation, I
2:34've shown you the two ways to do that, we
2:36actually going to need that trick in a
2:38second.
2:39Now, like I said, we need to comment our
2:41calculation. So I'm going to do it the old
2:43school way by doing a double forward slash,
2:45I'm going to say step one, need to find all
2:50items in furniture.
2:54Okay, that's step one. Step two,
2:58I then need to find
3:02everything in chairs.
3:06Step three, tag those as discontinued.
3:14And then step four, for everything else.
3:19Mark as discounted. Okay.
3:24So I've got four things I need to do here,
3:25I'll just make this larger so you can see
3:27what's going on, I'm just holding Ctrl, and
3:29moving my cursor, not my cursor, my scroll
3:32wheel on my mouse up and down.
3:34And that's essentially doing that for me.
3:36Now you can see here that I've got my logic
3:37set out. So now that I've got my logic set
3:39out, I can actually start testing the
3:41calculations and writing them.
3:44So, step two, when you're writing a
3:45calculation, actually write them underneath
3:47each of your comments. So you'll see me
3:49adding a space in between these
3:51calculations to make them easier to sort of
3:53spread out and see what's going on.
3:55And that's why I use this type of comment
3:59because I want everything to fit on one
4:03line. If I go ahead and put another space,
4:05let's write the logic for the top item here
4:05. So I need to find all items in furniture.
4:06Now there's a couple ways you can do this,
4:07the simplest way that most people do is a
4:09logic statement. So they basically say that
4:12if category equals furniture, and I'll just
4:17go type in furniture. Tabbo doesn't have
4:20autocomplete so this is not going to aut
4:22ocomplete for you, make sure you spell it
4:23correctly.
4:24Then what I'm going to do is basically tag
4:27them as found. So let's, let's just say
4:30found. And then else actually don't need to
4:32do an else here, I can just end that
4:34statement there. And that's going to be
4:36pretty good. So you can see that I've
4:38written my calculation there.
4:40And that's pretty much it. Now for
4:42everything else that is not furniture is
4:44basically going to leave it as null,
4:46because I haven't specified something at
4:49the end of that function. Now, what I could
4:52do is I could say then found else ignore.
4:55Okay. And that would also do the same thing
4:57, but this time instead of now I get the
5:00term ignore. Now if you want to see this
5:01very quickly, here's the trick that you
5:03should really use more often. Okay.
5:06If I just highlight this calculation here,
5:08what I can actually do is I can drag it and
5:10put it on text. And you can see that I can
5:13very quickly see my calculation working
5:15came so it's instantly gone and replaced
5:17all the text items of exactly what was in
5:20that calculation.
5:21And you'll see that my calculation is also
5:23in here as an ad hoc calculation. So if I
5:25double click it, I can actually go ahead
5:28and edit it inside of the ad hoc
5:30calculation. So by dragging it into one of
5:32the placeable items, anywhere you can place
5:35a pill or one of these Tableau calculations
5:38, green or blue sort of pills, you can go
5:41ahead and edit that calculation directly.
5:43So this is a really, really nice way of
5:44testing it. Let's say that I wanted to
5:46change this calculation. And I wanted to
5:48see the difference between this one and the
5:50previous one where essentially gives you a
5:52knowledge that have ignored, I can go ahead
5:54, I can remove this.
5:56So and here you can see the calculation is
5:58valid. Let's highlight this again, go back,
6:01put it on text. And now you can see the
6:02null is working because we've got this sort
6:04of additional space.
6:06What I can do is I can remove the previous
6:07one. Let's just remove the previous all of
6:10them in fact, and just do this again. So it
6:12's much, much clear to see. And you can see
6:14that everything else is left as null
6:16because essentially I didn't specify
6:18anything in them.
6:19So this is a really nice way of testing and
6:21calculation and making sure things are
6:23working as you progress. Now the other
6:25thing you can do is you can put this item
6:27anywhere in the columns and rows. So I'm
6:29actually going to put this up here.
6:31This is what I recommend people do when
6:32they're tagging things, they want to make
6:34sure that their logic is going to work. So
6:36write the different components of your
6:38logic, and then slowly add it all together.
6:42So the first thing was to check if the
6:44category equals furniture, then found, then
6:46need to find everything in chairs. So I can
6:49just, I can just basically essentially do
6:52this, I can go ahead and paste this.
6:55And what I need to do here is call this a
6:57subcategory. So let's just go ahead and
7:01type that and let's make sure this is
7:04chairs.
7:05Okay, and you can see here there's an issue
7:07because essentially it's I'm writing sort
7:09of two calculations simultaneously. So to
7:11avoid that issue, I'm just going to double
7:13comment that top one, so that this second
7:15one is valid.
7:16And now I'm going to test this. So I'm just
7:18going to grab this, and I'm going to grab
7:20and that and put it on text. And you can
7:22see that it finds the chair items there. So
7:25that's all working nicely.
7:26I can go ahead and put it next to my
7:27previous calculation just so that I can
7:29start to see them laid out really nicely.
7:32So you can see what I'm doing here, I'm
7:33doing a bit of trial and error.
7:35And I'm building this out as a table before
7:37I start doing it as a chart where it gets
7:39really complex really quickly.
7:41And essentially what I then need to do is
7:42tag those two things just discontinue. So
7:45essentially, you can think of this a bit
7:47like an and clause. So instead of writing
7:49this, I'm just going to put double forward
7:51slash here and just say and clause.
7:53So basically, I want to say if the category
7:55is furniture and the subcategories chairs,
7:57then found. Otherwise, mark everything as
8:01as discounted. So for this last one, I can
8:05just say,
8:06this this last one is kind of there's no
8:07need to test this because it's basically
8:09just the final condition. And I've already
8:11sort of got some experience with that
8:13because the final condition is essentially
8:16this addition where we just basically go if
8:18I just put this in a double forward slash
8:20else if discounted is essentially what I'm
8:23going to type, okay. And, and an end
8:25statement on that. So that's basically all
8:28I need to do. I can't test this because it
8:31's not a complete logic statement. Okay. But
8:33what I could do is I could go ahead and
8:35grab this.
8:35And put it at the end of this. And then we
8:39can just test this now. The reason I put an
8:42else if is because I was thinking ahead, in
8:44this case, I'm going to put an else and it
8:46will work completely fine. And if you're
8:48getting confused with any of this, go check
8:50out
8:50my video on Tableau functions. I've
8:51specifically covered these logic functions
8:53in a lot of detail. So that's really,
8:55really good video for this. If I then go
8:57ahead and we test this, I'm just going to
8:59go grab it, put it next to the previous one
9:01, you can see that the
9:03discounted tag is working. So I'm
9:04essentially just building these up, I'm
9:06just building out a very simple table. And
9:09it's working really, really nicely. So now,
9:11I can actually start to bring this together
9:13. And when I do that, I tend to just remove
9:16these forward
9:16slashes. And now I'm left with the sort of
9:18bare bones of my calculations. And I can
9:21actually start constructing it the way I
9:23want. So if category equals furniture, and
9:26then what I can do is and subcategory
9:28equals chairs. So let's just copy that and
9:31just type
9:32in an and clause there, and paste that in.
9:34And what I like to do is I like to format
9:36things in a clear way. Another reason why
9:38people get sort of deep into calculations
9:41is they just keep adding stuff and they
9:43haven't formatted it along the way. And the
9:46formatting
9:46doesn't break the calculations. What I like
9:48to do is put every single new thing I'm
9:50doing on a new line. So let's go ahead and
9:52say if category equals furniture, and sub
9:55category equals chairs, then found. And then
10:01for this one,
10:01I can basically remove this and clause here
10:03. And I can go and get this. And this isn't
10:05necessarily correct. But I'll paste it in
10:07and then we'll sort of figure out what to
10:09do with this. And we'll just go ahead and
10:11comment the old one out. So if category
10:16equals furniture, and the subcategory
10:17equals the chairs, then found. Now what I
10:20need to actually do is mark these as
10:23discontinued. So this discontinued and then
10:27else I don't need the else if else if is
10:30only needed if I need to sort of write
10:33multiple logic statements. So in this
10:36particular case, else is
10:38absolutely fine else discounted and then
10:40and so I'll just put the else on a new line
10:42. And I'll put the end on a new line. So
10:45basically, you can see this logic statement
10:47here is all on a new line for each new
10:49thing I do. So it's easy to read if
10:52category equals furniture and subcategory
10:54equals chairs, then discounted else
10:56discontinued, else discounted, well, that's
10:59a really big tongue twister there, using
11:02two words are very similar, but not the
11:04same. And then for everything else, you can
11:07see it's just basically zoned out because
11:10it's not a valid calculation.
11:11So we can actually go ahead and delete all
11:13of these. And I can just close this up. And
11:16what I like to do is put the formatting
11:18back at the top. So that anyone who opens
11:20this workbook in like three years time or
11:23never, which is most of the case with most
11:26analytical products
11:27is they're going to be able to understand
11:29what this means right from the get go. So
11:31let's have a look at this and see what's
11:33going on. So if category was furniture and
11:36subcategory was chairs, then discontinued
11:38else discounted and, and essentially, if I
11:41just go ahead and drag that there, and we
11:43put it next to our thing, we can actually
11:44see the tag working. So you can see that it
11:47's marked everything other than the chairs
11:49item, as discontinued. And that's me
11:53creating a logic statement and writing a
11:54calculation in a very simple way.
11:56Now, this has been a very simple
11:57calculation. If you've been using Tableau
11:59for a while, this is a very easy
12:00calculation to write, you probably don't
12:02even need to go through all the steps that
12:04I've gone through, because eventually you
12:06do get comfortable enough just writing this
12:09statement out without needing sort of the
12:11support that I've given you in this video.
12:13But nevertheless, if you're doing a more
12:15complicated calculation, you're definitely
12:17going to find this useful. It doesn't
12:19matter how experienced you are, it's just a
12:21fundamental part of making sure you build
12:23your calculations and test them in a way
12:25that's structured.
12:27And this is a nice little trick, we're
12:28using ad hoc calculations in the view. And
12:30once everything is there, of course, you
12:31can just get rid of them. So something that
12:33people will ask is, hey, can I then get rid
12:35of these? Absolutely, because those were
12:37just ad hoc calculations that we're using
12:40on the way to getting a final answer.
12:42And because this is the calculation I want
12:44to keep, I'll just call this tag, I'll hit
12:46apply. And when we hit that apply button,
12:48and actually goes over here to the left
12:51hand side. And you can see that I can
12:52actually bring it in. And it's got a nice
12:55field name to it tag.
12:57And but my existing calculation here is
12:59still there, let's say that I wanted to
13:01bring back one of those previous
13:03calculations, and there was still here,
13:05what you can do, if I close the calculation
13:08window, you can see that this is still here
13:10, what you can do is you can drag this and
13:13put it here on the left hand side and Table
13:13au will assume that you're trying to create
13:14a calculation from the ad hoc calculation.
13:16So then I can call this tag v2, and enter
13:18and you'll see that it renames this one
13:21here on the top and renames this column as
13:23well. So so many flexible ways with working
13:26calculations. And hopefully you found this
13:29video useful and it's been maybe showing
13:30you something you didn't know about Tableau
13:32.
13:33Thanks for watching. I'll catch you in the
13:34next video. Be sure to check out the
13:35functions playlist that I have set up for
13:37this function and many other functions, as
13:39well as other videos that I have on the
13:41channel. And I'll catch you in the next one
13:43. Thank you.
13:44Thank you.
13:45[beep]
Tableau | Data visualisation