How to write calculations clearly in Tableau: Desktop & Web authoring

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]