What is the Lookup function in Tableau? Tableau Functions

0:00Hey, it's Tim here. In today's video, we're

0:01carrying on with a Tableau Functions

0:03playlist

0:03that I've been building out. And today we

0:05're going to be looking at the lookup

0:07function. This is a

0:08big function. So I wanted to sort of do it

0:10justice and do it properly. So we're going

0:13to jump into

0:13Tableau in just one second. But before we

0:16do, if you do look at any of my videos, you

0:18might not

0:18know that there's a website tableau term.

0:20com. And actually there I have all my play

0:22lists and all my

0:23videos. Now what's better about my website?

0:25Well, there's actually a playlist page here

0:27. And you can

0:28go to a particular playlist, for example,

0:30Tableau functions where you'll find this

0:32video. And you

0:33can see that I've got all the previous

0:35functions. And better yet, when you click

0:37on one of the

0:37functions as a simple example, you not only

0:39get the same video that you'd see on

0:41YouTube,

0:42but you actually have this handy way of

0:43browsing them here on the right hand side.

0:45So you can just

0:46click on one, and it takes you straight to

0:48the video without leaving the page. So you

0:50can

0:50essentially learn all in one place if you

0:52just want to get to know functions get

0:54involved. If

0:54you want to find out some new features in

0:57one of the other versions of Tableau that's

0:59been released

0:59recently, go ahead and get involved. You

1:01want to know about Tableau file types, I've

1:03got a playlist

1:04on that too. You can click on that and you

1:06can get involved. Now not all of them have

1:08thumbnails,

1:09I'm still working through editing the

1:10website. But nonetheless, everything that I

1:12've made is actually

1:13there, especially on the homepage where you

1:15can just see the videos as they come in. So

1:17I think

1:17this is a better place to watch my videos.

1:19But if you're here on YouTube, that's also

1:21fine. Thanks

1:21for tuning in. Let's get stuck into this

1:24lookup video. Okay, so we're going to hop

1:26into Tableau.

1:27And for this, I'm just going to use Super

1:29store sales. A lot of people asked me for

1:30the data set

1:31that I use in the video. It's just inside

1:33of Tableau. So I'm going to make sure I

1:35call it out.

1:36Over here on the bottom left hand side,

1:38there's some saved data sources. Now you

1:40might use the EU

1:41version, you might use the American Super

1:43store version. If you don't see these, let

1:45me know in

1:46the comments. And I'll give you a link to a

1:48version of the file that you can download

1:49and use. But

1:50nevertheless, I'm going to go ahead and

1:52click on sample Superstore here. And it's

1:53just going to

1:54open up the Save data source, ready to go.

1:56Now the reason I want to use this file is

1:58because everyone

1:59has access to it. And it's got everything

2:01we need in order to show you this feature.

2:04Now, I'm going

2:04to build out of view very, very quickly.

2:06And I'm going to deliberately set it up in

2:08such a way that

2:09might represent something we might do in

2:11day to day analysis. So I'm going to grab

2:13all the dates

2:14and put it on rows. And for this, what I

2:16want to do is only keep two years worth of

2:19data. So I'm

2:19just going to select two by holding shift,

2:21keep only and that's it, although all we're

2:24going to

2:24keep is two years worth of data. The next

2:26thing we're going to do is open up the

2:28product hierarchy

2:29here and bring in the subcategories as well

2:31. And for the subcategories, I'm actually

2:33also going to

2:34bring in the categories in front of it. And

2:37I just want to keep furniture and

2:39technology. The reason

2:40is, is because I don't want this table to

2:42get too big, I want to be able to show you

2:43all the values

2:44very, very clearly. Now the last thing I

2:46want to do is bring in the months for the

2:48years. So for

2:49this one, I'm going to right click all the

2:51date. And this doesn't actually work on a

2:54Mac on a

2:54Windows, if you right click drag, it does

2:56actually bring it out. So I'm just going to

2:58drag it out onto

2:59the canvas. And I'm going to go down and

3:01select the month. And that's what we want.

3:04So now you

3:04can see a grid table with ABC all over the

3:07place. And the last step here is to add

3:10sales, put it

3:11into this white space here. And now you

3:13have a set of numbers. Now this is just a

3:15total sales,

3:16it's just an aggregation. I know it's an

3:18aggregation because of this here, it says

3:20sum of sales. So everything is being aggreg

3:23ated for each subcategory for each month.

3:26And it's got

3:27a hierarchy with the year on the left and

3:29category on the top. Okay, so we're pretty

3:32much set. Now

3:33I'm ready to use the lookup function. So

3:35with all functions, you pretty much go in,

3:38create a

3:38calculation. And I'm going to keep this

3:40calculation window open here on the right

3:42hand side. And I'm

3:43going to try and make this bigger so you

3:44can see what I'm actually typing, okay. And

3:46so with all

3:47the functions, you can just start typing,

3:48and it automatically pulls it up. So let's

3:50just type in

3:51lookup. And you'll see that the function

3:53appears straight away. Now I've also

3:55already created a

3:56lookup calculation in this. So we're just

3:58going to ignore that for now. And we're

4:00just going to go

4:01ahead and type that in just like that. Now,

4:04obviously, this lookup function has a way

4:06that

4:06it has to be written. So you can see here

4:08that it's asking for the expression, and

4:10then it's asking

4:11for the offset. So let's break that down in

4:13very, very simple terms, okay, the

4:15expression is just

4:16going to be the aggregation. In our case,

4:18the expression, the aggregation is just the

4:20sum of

4:21cells. The reason it's the sum of cells is

4:23because when you're doing a lookup, you

4:25typically

4:26comparing it to like minded value. So for

4:29example, if I wanted to compare the sum of

4:31cells versus the

4:32previous month, I'd also be looking at the

4:35same aggregation, the sum of cells for the

4:37previous

4:37month. So in this case, we are going to be

4:39using some of cells, I can actually hold

4:41command and

4:42drag it in to the view. And that just

4:44copies it as a calculation into the

4:46calculation. And then we can

4:48just go in here, type in a comma. And you

4:50can see that Tableau is hinting what I

4:52should be typing

4:53here, you can just see that it appears here

4:55, just as I as I start typing, it disappears

4:57when I go to

4:58annotate it. But as I put my mouse there,

5:00and comma, you'll see that it suggests it

5:03again,

5:03in that exact space. So the offset here, I

5:05'm just going to type in one, I don't want

5:07to go too crazy,

5:08I just want to make this simple for

5:09everyone to follow, I'm just going to make

5:11it larger. So you

5:12can see this working. And now you can see

5:14that the calculation is valid, it says that

5:16just down here.

5:18And that means we've written a good

5:19calculation. And we can go ahead here and

5:21give it a name.

5:22And I'm going to call it lookup demo, just

5:24not to clash with a previous calculation

5:26that I wrote.

5:27Let's hit Apply. And that's our calculation

5:30, you can see that it's not only a change

5:32name up here,

5:33but that has also been sent over to the

5:35calculation window over here on the left

5:38hand side. Okay, so

5:39now we're ready to work with this. In order

5:41to see this in the table, and to see what

5:43it's doing,

5:44I'm going to drag the lookup into the black

5:46box again. And what this will do is it will

5:49actually

5:49do a couple of things, it goes crazy, Table

5:52au tries to help us out here. And what it

5:54does is

5:54it creates something called measure names

5:56and measure values. If you missed that step

5:58,

5:58you wouldn't have seen measure names here,

6:01measure names there, and measure values be

6:04swapped into

6:04the visualization to essentially create

6:06this table. And the way this table now

6:08works is we've got two

6:09sections, we've got the lookup demo section

6:12up here, and our sales value, which just

6:14goes off

6:14screen here, I actually don't want it there

6:16. So what I'm going to do is I'm going to

6:18change the

6:19orientation of this, I'm actually going to

6:20grab measure names and put it next to subc

6:22ategories.

6:23So I have the values side by side. The

6:25reason I want this side by side is because

6:27it's going to be

6:28easier to show you what's going on. The

6:30very last step is I'm going to grab measure

6:32names, I'm going

6:33to hold command and drag it onto color. Now

6:35if you're on Windows, if you hold Ctrl, it

6:38does

6:38exactly the same thing. And now what this

6:40does is it puts the colors on different

6:42measures. So I can

6:44easily show you what the lookup function is

6:46doing in blue, and what the normal sales

6:48aggregation is

6:49doing in orange. I actually don't like

6:51those colors. So let me just move this down

6:53here. And

6:53let's double click that. And let's just

6:55change that a little bit. So you can see

6:57here that it's

6:58not having me double click the color whilst

7:00I've got that open. So let's do this, let's

7:03go to sales,

7:04and make this blue and make the lookup red.

7:06So it's just easier to see what's going on.

7:09And let's

7:09click that out. Now, we've got our lookup

7:12function, we've got it working, what is it

7:15actually doing?

7:15Well, at this point, what I'll do is I'll

7:17open up the calculation again. And I'll

7:20expand this little

7:22arrow here. Now this arrow is very, very

7:23subtle, you might not see it normally. But

7:25if you don't

7:26have this open, definitely open that

7:27because we're going to need the description

7:29for the lookup

7:30function. So let's go in here and type in

7:31lookup. And you'll see here on the right

7:33hand side returns

7:34the value of the given expression in a

7:37target row specified as a relative offset

7:40from the current

7:41row. Okay. I'll explain that in English in

7:44a second, use first plus n and last, in

7:48brackets

7:48minus n for a target relative to the first

7:51and last rows in the partition. If offset

7:54is emitted,

7:55the compare to row may be set on the field

7:58menu returns now if the target row cannot

8:01be determined.

8:02Okay. So in this case, we're just going to

8:04try and figure this out slightly. All right

8:06, we're going

8:07to just try and work with this together.

8:10Okay. Let me first figure out what is this

8:12one doing?

8:13What's this one actually doing? So this, if

8:15you remember, is our offset, you can see

8:18that it's

8:18explaining that over here. So it's just got

8:21this little example over here where it has

8:23a slightly

8:24more convoluted example. But in here, it

8:26talks about an offset. And this one is

8:28basically our

8:29offset. So if we look at this table, we

8:31just bring our annotation tools up, and you

8:34'll see that it

8:35says something here about moving table

8:37across. So I'll use that hint and see what

8:39's going on

8:40table across to me means going from left to

8:42right. So let's look and see what's

8:44happening.

8:45You've got the January figure here 4188.

8:48Okay. And then if I go across to sales,

8:51that says 1000. So

8:52for January, the sales value is 1000, the

8:56lookup is 4188. If I go to the next month,

8:59it's 712. But

9:00the sales value is 4188. And so I can see

9:04that this 4188 is equal to that value over

9:07there.

9:08So you can start to sort of see what's

9:10going on here, it's looking back or looking

9:13ahead one row

9:14in this particular case, and it's grabbing

9:16that number and it's bringing it back. So

9:18just the one

9:19essentially a plus one looks forward one

9:22right? Let's see if that's true. Again, let

9:24's go to 712.

9:25It looks forward. And there you go, you can

9:28see that that's working. Okay, so what this

9:30lookup

9:30function is doing, remember, we're talking

9:32in the context of the lookup function, not

9:34the sales

9:34function, is that it looks at the current

9:37row, it will go forward, grab the next

9:40value, and then

9:41bring it back to this particular right,

9:43that's all it's doing. Okay. Now, in the

9:45context of how this

9:47is set up, the lookup function is always

9:49going to be doing it in in its own context.

9:52So for example,

9:53it's not going to be looking at the sales

9:55value, in this case, it's only going to be

9:57looking at

9:57itself and bringing the values back. But

10:00remember, the expression, the sum of sales

10:03is actually what

10:03it's using to do this math. So in essence,

10:06it's doing exactly the same as sum of sales

10:09,

10:09but returning the value that should have

10:11been there, but it's offsetting it by a row

10:15by

10:15essentially one, which is what we've set

10:17here. So as we continue to look at this, we

10:20can see very,

10:20very easily what's actually going on. So

10:23this 333 ends up being this value here,

10:26this 712 ends up

10:28being this value there. And then this 4188

10:31ends up being this value there. So that's

10:34table across,

10:35sort of explained. What happens here at the

10:38end? Well, at the end, we have a slight

10:41problem,

10:41because you can see here that there's

10:43nothing in this row. And so let's just

10:45clear the annotations

10:46and move this across. In fact, there's

10:48nothing in this entire row. So what's going

10:51on here?

10:52Well, remember, when it can't find a value,

10:55it will return now, he says that over here.

10:58So you

10:58see here, it says, returns now, if the

11:00target row could not be determined. And so

11:03because I'm on

11:04this very first row, I'm actually starting

11:06here on the right. And I look ahead one

11:08month, well,

11:09there's nothing else, there's sort of

11:11nothing in this direction for it to grab a

11:13number from.

11:13And so it returns now. However, when we go

11:16back a row to this column, if it looks back

11:18one row,

11:19there is a number. So now it's able to grab

11:22that value and return it. If we go back to

11:24this one,

11:25you'll see there's actually no sales value

11:27here. So when it looks forward one row, it

11:29finds nothing.

11:30So the value of nothing is nothing when you

11:32look back one right. So essentially, it's

11:35doing exactly

11:35as described. But the column that is sort

11:37of different is actually just this one here

11:39. So

11:40I've drawn tons of arrows, let's clear them

11:42all. And let's reset our thinking here. So,

11:45so far,

11:45so good. Hopefully, you understand now how

11:47the lookup functions working, you're

11:50essentially

11:50telling Tableau to look, maybe one row back

11:52two rows back, however many rows back you

11:55wanted to

11:55look. And you're also controlling the

11:57direction. Now, at the moment, you see here

12:00, it says table

12:01across. And so you might be wondering, well

12:03, can I make it work in another direction?

12:06Absolutely,

12:06you can. Let's close this. And we're going

12:08to now focus on a different part of this

12:10lookup function.

12:12But go over here to the left hand side, you

12:14'll see there's a little triangle. And if I

12:16click on the

12:17triangle, and I hit this down arrow that

12:19appears in its place, you'll see that I get

12:21a drop down

12:22option. Now this drop down option has a

12:24section called compute using. Now I'm not

12:26done a video on

12:27this yet. But trust me, I'm going to be

12:29doing a proper video on this because the

12:31window function,

12:32so many functions use this, like naming

12:34structure to make sure that they're

12:36calculating properly.

12:38Okay. And the ones we're going to focus on

12:40here are the ones that say table and pain

12:43because

12:43essentially, those are pretty consistent

12:45across all visualizations. The ones at the

12:48bottom,

12:48which say cell category, order, day and sub

12:51category depend on what's being used in the

12:53visualization. To be honest, the cell is

12:55actually always there. But the category

12:57order date and

12:58subcategory are only there because we're

13:00using them in our visualization. Okay, so

13:02we're using

13:02category up here, subcategory there. And

13:05then we're also using year order date and

13:07month order

13:08date there. So that's why these are

13:10actually appearing in here, you've just got

13:12all the day

13:12as one field, but essentially, you've got

13:14different iterations of the day in the same

13:16field.

13:17Okay, so let's change this to table down

13:19and see what happens instead. So let's look

13:23at this value.

13:24So if we go back in here, now I'm going to

13:25use blue just to change things up, you've

13:27got the

13:281706. Okay, and then you've got 308, then

13:32you've got 641. And if we're working on

13:35table down,

13:36what it's actually doing is it's looking at

13:38this number, you can also see there's this

13:40perfect

13:40relationship this way. So whereas before is

13:43working from I'll show you this in red,

13:46left to

13:46right, and it was basically bringing the

13:48value back, this one is working slightly

13:51differently,

13:51it's going down. So it's going to the

13:53valley that would be in June and returning

13:56it in May.

13:56And so it's sort of filling the table

13:58backwards this way, okay. And this is super

14:01handy, because

14:02when it essentially keeps going like this,

14:05you'll see that 304, 1805, 2804, it goes

14:09all the way

14:10until the bottom. And of course, the very

14:12last row here hasn't got a value, you

14:15probably know why,

14:16because it's not. And so these will all be

14:18now because they're right at the bottom of

14:20the table.

14:21So there's no other number that it can pull

14:23from. If you're not clear on that, let me

14:25just repeat

14:25that again. These values here in December

14:28cannot pull a value from below December,

14:32because there's

14:32nothing after December, I filtered the data

14:35out. If I was to add 2020 back in this

14:38would fill up.

14:39But because we've removed it, there's

14:41nothing there for it to look up. So in that

14:43event,

14:43it's always going to return now in these

14:46spaces. Okay. So we've done table across,

14:49we've done table

14:50down. Now, what if we were doing a

14:52different kind of analysis, let's say we

14:54wanted to look at the

14:56previous year's sales, but we wanted this

14:58value to sort of continue or restart every

15:01single year.

15:02Well, if I look at the little junction

15:04point that we've got here, you can see that

15:061805

15:07starts, and even though it's across the

15:10years, so if I go to December, here's

15:12actually looking ahead

15:13at January. And if I go here, it's looking

15:16across the years as well. So this

15:18calculation at the

15:19moment table down is just looking all the

15:21way down. So can we change that? Well,

15:24absolutely. If I go

15:25back in here to this drop down and say

15:27compute using, you'll see there's a few

15:29more different

15:30options we can choose. Now, in Tableau, a

15:33pain is considered something like this. So

15:37I'm just going

15:37to draw an example of a pain, you can

15:40consider this a pain and an essence. And

15:42the reason it's

15:43a pain is because essentially, it's almost

15:46a subgroup of the year in this particular

15:49case.

15:49So this is one pain, and this is another

15:51pain. So when we talk about the pain in

15:54this context,

15:55we're looking at some sort of context here

15:57in the visualization. Now, the pain also

15:59works on both

16:00rows and columns. So in this particular

16:03case, another pain would be this one here.

16:05So you've

16:06got bookcases, then you've got the value,

16:08because of course, the bookcase is sits

16:09within furniture.

16:10So this would be a pain. And as you work

16:13away across the vis, you start to

16:15understand how

16:16pains and rows work. So when we look at

16:18table down, and table across, it's working

16:21using columns

16:22and rows, essentially, when we look at

16:24pains, it's using sections in the table. So

16:26let's have a look

16:27at this and look at pain, pain, pain across

16:30, then down. Okay, so let's do that. And you

16:34'll see the

16:35values change. So what is happening here?

16:38Now let's grab our arrow, remember, we're

16:41selecting

16:41pain across then down. So if we go down

16:44here, you'll see 1706. Let's make sure we

16:47choose the

16:48arrow 1706, 308, 641. We keep going and

16:52keep going 726, 3054. And then it stops, it

16:56doesn't

16:57grab the value from this row. And then for

17:00January, it grabs the value of 1805 and 280

17:054. And so the

17:07real question is, where is it getting the

17:09January value of 2804? Well, of course, it

17:12's getting it

17:12from there. So it started again, but this

17:15105 figure never makes anywhere in our

17:17visualization,

17:18essentially, it stops at that point, if we

17:20go to the previous row, you can see it does

17:22the same

17:23thing. Now, if we go all the way to the end

17:25here, you can see that it's also doing the

17:27same thing

17:28here. So essentially, the context is always

17:31within the pain. Now, you can arrange a

17:33table in lots of

17:34different ways. And you can essentially go

17:36crazy with these. But in essence, all you

17:39need to

17:39remember is that whichever direction you're

17:41choosing, this is always going to change

17:43the

17:43way that lookup function is working.

17:45Essentially, you give it the calculation,

17:47and then you give it

17:48an offset for the direction that you want

17:51it to go. So let's go here and do down then

17:53across. Now,

17:54you'll see that nothing really changes.

17:57Well, because the thing here is the pain is

17:59still

17:59being constrained by this particular

18:01context. So the pain hasn't really changed.

18:04Even though I've

18:05changed that setting, the pain is still

18:06being defined as this, there's no way for

18:08it to sort

18:09of break out from that. So whichever

18:11direction I set it in, it's not really

18:13going to change,

18:14it's just always going to stay within that

18:15context. So that's one thing about you know

18:18,

18:18the pain, sometimes you can sort of click

18:19on these. And you don't really see anything

18:21changing really.

18:22And it's because it's just because the way

18:24the function works, the way the pain is

18:26described

18:27has been sort of locked in. And so that's

18:29not going to change unless you change the

18:31data

18:31structure or you change something in the

18:33visualization to make the pain larger or

18:35smaller.

18:36Okay. Now, what I could do is I could

18:39collapse the category like this. And I

18:41could make the table a

18:42lot simpler. Alternatively, I could move

18:45the measure names over here. Okay. And now

18:47you can

18:48see this is a very weird table because now

18:51I've got the values over here in a slightly

18:54different

18:54setup. And it doesn't look so good. But you

18:56can tell here that there's actually a

18:58problem. The

18:58problem here is that there's no values for

19:00the lookup function, you're probably

19:01wondering, well,

19:02what's gone on here, what's broken. And

19:05again, it's just very simple. We've got

19:07this lockdown

19:08to pain down. And the pain in this

19:10particular case is tiny. It's just this

19:13area. So it can never,

19:15ever go to another row, because essentially

19:17, looks at the first row and then stops. If

19:20we were to

19:20change things about let's say I move month

19:22over to the category here, the exact same

19:25problem happens.

19:26Why is that? Well, because we've got the

19:28pain going down. So because it's going down

19:31,

19:31it doesn't matter that we've got all these

19:33months going across the table, it's always

19:35going to be

19:36working in the vertical direction. So the

19:38pain is still defined as this. And it's

19:41still going to be

19:42working in this direction. So it's kind of

19:45tricky, because essentially, you can I know

19:48, this happens

19:48to so many people, you can sort of click

19:50around and just go, why is this not doing

19:52anything. And

19:53it's just fundamentally just the way the

19:55table has been built, the way that's been

19:57locked in.

19:57Now, we can change things up a little bit,

19:59by sort of making this more interesting,

20:02what we could do

20:03is we could decide to bring in a category

20:05subcategory in front of measure names. And

20:08when

20:08we do that, we still have the same problem.

20:11Why? Because the pain is still defined in

20:14exactly the

20:14same way. This is still the pain. So you're

20:17probably wondering, Oh, God damn it, what

20:19is

20:19going on here? How can I get this pain to

20:21work slightly differently. And essentially,

20:24it requires

20:24us just to rethink how we're building this

20:26table. Most people have probably done this

20:28in frustration.

20:28But hopefully, I'm trying to show you why

20:31it's behaving this way. And you can sort of

20:33fight your way out of this. Let's try and

20:35fix this table. Okay, I've cleared the

20:38annotations. And now

20:40what I'm going to do is I'm just going to

20:42try and get this table back to way where it

20:44's working. And

20:45it's doing what we expect it to do. I'm

20:46going to put measure names back on here at

20:48the top. So you

20:49can start to see something going on. And

20:51when I do that, you immediately see things

20:53start to sort of

20:54behave again. So remember, what's changed

20:56here is that I moved measure names from

20:58this position,

20:58where it was controlling the pain, don't

21:01forget the measure names value itself, the

21:04header can

21:05actually create a pain in its own right.

21:07And if I move it to the top, and it starts

21:09looking down,

21:10well, because I've moved it to the top, and

21:12it starts looking down, look downwards,

21:15this measure

21:16names item is no longer disrupting the

21:18downward motions of the calculation. So

21:21whereas before,

21:21if I just, if I just come out of this and

21:25move this down here, you'll see that

21:28essentially,

21:29you can see here, accessories, and then it

21:31goes into an even smaller pain here, and it

21:33's not going

21:34to do anything as soon as I move it up.

21:36Suddenly, the calculation can break free

21:38from accessories.

21:39And now the pain is looking within 2018.

21:42And it's moving down. And now it's doing

21:44exactly what I

21:45thought it would do, which is doing this

21:47and going back one at a time. Of course, we

21:49've got some

21:49missing values here, that's expected

21:51because of the way I've sort of cut the

21:53data up. But

21:54nevertheless, you can see that everything

21:56is working like it should. So part of this

21:58is to

21:59do with how you set up your table, how you

22:01make everything sort of work together. And

22:04also, it's

22:04about making a table that makes sense. So

22:07what we're going to do is just try and

22:09reset this table

22:10a little bit. So it's a little bit more

22:12compact, and it works a little bit better.

22:14Earlier on,

22:14I actually had a year and month in the same

22:17axis. So you can see that's working a lot

22:19better there,

22:20then I'm going to bring subcategory back

22:22here to the top. And now that works a lot

22:24better. So

22:25when you're building tables, try and get

22:28your table to a place that works first,

22:30and then worry about all the different sort

22:32of setups that you're going to use.

22:34If you wanted to rotate this sort of layout

22:36, you can of course do this. And watch as I

22:38do that

22:39tablet actually flips the lookup function

22:41and gets it looking to the right, that's a

22:43little tricky,

22:44you might not know. Remember, we had pain

22:46down then across, and then now it's flipped

22:49it to pain

22:50across. So you got to remember when you

22:52flip a table table is also going to flip

22:54this direction

22:55in the instruction. And that's why it

22:57changed color because to tableau that was a

22:59slightly

22:59different calculation. If I flip it back,

23:01if I just click this and flip it back, it

23:03goes back

23:04to being red, because of course, it's the

23:06same calculation we had before. So the

23:08coloring is also

23:09hinting to you that something's changing.

23:11There's a lot of little sort of tips and

23:13tricks that are

23:14sort of hidden there for you to look at.

23:16Okay. So so far, we've done is use this in

23:19a table setup.

23:21What we haven't done is use this in a chart

23:23. So let's build a chart and see how this

23:25works.

23:26Let's go over to a new sheet here. And we

23:27're going to use a much, much simpler chart,

23:29we're just

23:30going to bring order date onto columns. Now

23:32when I do that, I actually want to bring a

23:35continuous

23:36month into the context here, and not into

23:38the context into the axis and columns here.

23:41And

23:41because I want to see each and every month

23:43spread out across a range, essentially. Now

23:46, if I bring

23:47sales onto this, you'll see that that's

23:48there. And now we're pretty much ready to

23:50go. I'm going to

23:51use the same lookup function I was using

23:53before, I'm not going to build another one,

23:55I'm going to

23:55use the same lookup demo value. And I'm

23:57just going to place it here. And you're

23:59going to see that it

24:00draws a line chart. But if you look super

24:03close, it's almost a splitting image of the

24:06line chart

24:07above. And you can kind of see this working

24:09in earnest here, because this data point is

24:12essentially this one here, and that one is

24:15essentially that one. So the offset of one

24:18that we've got has sort of shifted it back.

24:21Now, if we open up this calculation, let's

24:24open this up,

24:25we can play around with this a little bit

24:27more. It won't open now it will. Tableau

24:30has been a

24:31bit buggy recently, I don't know what it is

24:32, maybe it's just me and my Mac. But I've

24:34definitely found

24:35it's been buggy recently. So that can cause

24:38a demo is to go right, let's change this to

24:41six.

24:41In this case, what we're going to do is try

24:43and shift this back six months. So let's

24:45hit six,

24:46hit apply and see what happens. It goes

24:48back six months. So when we were adding the

24:51number one,

24:52we kind of set it up in the table that made

24:54it look like it was looking forward. But

24:56actually,

24:57in this context with dates, you can see

24:59look up with a number six actually means it

25:01's going to go

25:02backwards, it's moving, it's shifting

25:04everything backwards. And that's just

25:06because of the way the

25:06function works. Essentially, it's starting

25:10at this position, okay, starting at this

25:13position,

25:13and it's going and looking back six rows,

25:16and it's grabbing that value, and it's

25:18showing it okay. So

25:20the data point here, okay, is basically

25:22going forward six rows, grabbing the

25:25numbers and then

25:26bringing them back. That's probably a

25:27better way of explaining it than the first

25:29time I just explained

25:30that. Okay. So although it's it's sort of

25:32bringing the data backwards, it's actually

25:35has this net

25:35effect of shifting everything into a

25:37counterintuitive direction. But it's easy

25:39to

25:40understand once you can see it here in the

25:41visualization. Now, what I'm of course

25:44doing

25:44is just setting a previous value. If you've

25:46ever used tableau metrics, you will know

25:48that this is

25:49exactly sort of what it does, it just

25:51compares this value to the previous month.

25:53So I can of course,

25:54go and do 12. And in the context of this

25:57visualization, this is just looking back 12

26:00months.

26:00So if I decide to make this a dual axis,

26:03and do this, where we are dual axis, and we

26:07make sure we

26:07synchronize these, so they're all set to

26:09the same axis, then you can see that this

26:12is basically just

26:13comparing to the previous year, because the

26:15offset is 12. We're basically looking back

26:18a year. Now,

26:20there are a couple of ways you can sort of

26:22play around with this in the visualization.

26:24Of course,

26:24you can create a chart like this. But more

26:26importantly, you can actually do a value or

26:29a

26:29chart, which does which looks a little bit

26:31more something like this. So I can sort of

26:33say, grab

26:34this value of cells, okay, and take it away

26:38from the previous value, which is 12 months

26:42previously.

26:42So if I do that, cells minus lookup, you'll

26:45actually get a chart that shows you the

26:47difference

26:47between cells. And so what I can actually

26:49tell you now is that look, in December, we

26:52are 13,000

26:53sales in terms of sales, we have 13,000.

26:57And behind where we were in the previous

27:00year,

27:01I think that's the correct way of saying it

27:03. Did I say that correctly? Let me let me do

27:06this. Let

27:07me just double check. So this is December.

27:10That is December 83000 96,000. Yes. So that

27:14is correct.

27:14I've sort of set it up the wrong way.

27:16Because I should have actually typed it

27:18like this,

27:18I should have actually grabbed this, pulled

27:21it over here and done a minus and then put

27:24the sum

27:24of cells there. So what I'm doing there is

27:26I'm doing an ad hoc calculation, you can

27:28see there

27:29that it's gone completely crazy. Let's just

27:32go back one. And I'm just going to type

27:34some of cells

27:35just to be on the safe side. Because I

27:37think when I drag something and dragged two

27:39values, I didn't

27:40want that I just want one. So that's great

27:42and hit enter. So there we go. That's a

27:44better way of doing

27:45it. So now it's sort of working in the

27:47right direction. And now this you can see

27:49is 13,000

27:51behind the previous value 96 minus 83. So

27:55what this does is it starts to show you the

27:58differences

27:58more clearly, year on year per se. Now, you

28:01've obviously got the problem that you don't

28:04have

28:04this data for more the more recent months

28:06because you don't have 12 months of history

28:09yet to be able

28:09to compare. So that's what these 12 miles

28:11essentially are, whenever you do a lookup

28:14function,

28:14you are bound to get something like this,

28:16which has some sort of lag or issues where

28:19the null

28:19values are going to be there. And remember,

28:21our lookup function will generate nulls

28:23where it can't

28:24find a value. And so where it can't find a

28:27value, it's just going to basically return

28:30now. Now,

28:30you could try and fix this because at the

28:33moment, it says no. But what you could do

28:36is you could actually put a zn function

28:39around the lookup. And what will happen is

28:42the zn function

28:44returns zero when it finds a not. And so

28:46what's actually happening here is zero

28:48minus some cells

28:50is going to return something, but now minus

28:52some cells is going to return a now. So if

28:55you put a zn

28:56around the now, what we should see is we

28:58get this, essentially. So we fix that

29:01problem by putting a

29:02zn function around the lookup. And what

29:05essentially happens is, where we have no

29:07data, this value gets

29:09increasingly worse, because of course, we

29:11have sales happening here, with nothing

29:13available for

29:14them to sort of compute against. Now we

29:16still have nulls, we still have nulls here

29:19being generated.

29:20But these nulls refer to these nulls up

29:23here, it's not actually the nulls that were

29:26created sort of

29:27down here. So whereas before, we didn't

29:29have this section down here in the bottom,

29:32we do have values

29:33now. But these 12 nulls are referring to

29:36let me just draw this arrow, it's super

29:38clear, referring

29:39to this space up here. Okay. So that's how

29:42it starts to look when you're charting, you

29:44can sort

29:45of start to make sort of creative use of

29:47this, you can start to use this in tables

29:49and charts,

29:50line charts, even bar charts, you can start

29:52to use this to compare against different

29:54values,

29:54to set targets. And all of this stuff is

29:56really super dynamic. Now, I'm just

29:59scratching the

30:00surface here with the lookup function. And

30:01there's so much more we can do. There's so

30:04many different

30:05variations we can do. So if you'd like to

30:06see a video on the different variations and

30:08ways we can

30:09use lookup, let me know in the comments

30:10below. But I think this has been a long

30:12enough video,

30:13I just wanted to do something to get this

30:15function onto the playlist. And then what

30:16we can do is

30:17revisit all the functions with questions as

30:19of course, people get back to me with

30:21things they

30:21want to see more of. So thanks for watching

30:27, and I'll catch you in the next video.