Tableau | Data visualisation | Analytics What is the Lookup function in Tableau? Tableau Functions
LOOKUP looks simple — give it an aggregation and an offset — but the real magic, and the real headaches, are all in the direction you tell it to compute.
- LOOKUP takes two arguments: an expression (usually an aggregation like SUM(Sales)) and an offset that tells Tableau how many rows forward (+) or back (-) to fetch the value from.
- Compute Using controls direction — table across works along rows, table down along columns, and pane options confine the calculation to a sub-section of the view.
- When LOOKUP can't find a target row it returns NULL, which is why you get gaps at the start or end of a partition where there's no data to pull from.
- The way you build your table or chart locks in the pane, so a calculation can appear 'broken' simply because the layout constrains it — restructure the view to fix it.
- Wrapping LOOKUP in ZN() converts those NULLs to zero, which is useful when subtracting a previous period to show year-on-year differences cleanly.
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.
The lookup function is a handy calculation you might use to compare today’s metrics with previous months years or even custom offset periods. In this video, I show you how it works as we continue to cover functions. Tableau Notes: https://help.tableau.com/current/pro/desktop/en-us/functions\_functions\_tablecalculation.htm#lookupexpression-offset Returns the value of the expression in a target row, specified as a relative offset from the current row. Use FIRST() + n and LAST() - n as part of your offset definition for a target relative to the first/last rows in the partition. If offset is omitted, the row to compare to can be set on the field menu. This function returns NULL if the target row cannot be determined.