What is the Window function in Tableau? Tableau Functions

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

0:02going to be looking at the window function.

0:04Now,

0:05the difficult thing with this function is

0:07that it actually applies to a whole range

0:08of functions in

0:09one go. So what I'm going to focus on

0:11purely in this video is just to describe

0:13how the window

0:14capability of any window function works.

0:17And we're going to use a sum a window sum

0:19essentially to

0:20show this behavior because aggregations are

0:22probably easier when they're done in sums.

0:24That's how our brain naturally works, we

0:26like to add things up. So we're going to

0:28hop into Tableau,

0:28and I'm going to show you how this works.

0:31Okay, so for the record, I always use the

0:33same data source,

0:34I'm always using Superstore sales. And you

0:36can get to it by selecting one of these two

0:38data sets,

0:38you won't have exactly the same numbers as

0:40me. But if you use any of these two data

0:42sets,

0:42you'll have all the same columns and fields

0:44. And you'll be able to follow along just

0:46using this

0:47data set. So let's go ahead and click on

0:49the second data source here, this actually

0:51gives us

0:52the global Superstore sales. This tends to

0:54be the data source that most people have

0:56installed on

0:56their machine. Now, in order to show you

0:59this, I want to build a very simple table.

1:01So I'm going

1:02to grab sales and place it on text. And

1:04then I'm also going to grab the subcategory

1:07and place it on

1:08rows. And this will simply show us the sub

1:10category totals. There's an aggregation

1:13working here for

1:14some of sales that's being shown here

1:16across all these rows. Now the next thing I

1:19'd like to do is

1:20to grab the date. Now when I grab a date

1:22and put it on columns, Tableau defaults to

1:25the year. And

1:26so you can see here that it's essentially

1:27aggregating to the year and it's built us

1:29this

1:29nice crosstab. Now we don't like tables in

1:31Tableau. Coincidentally, that's why tables

1:34don't perform too well here in this data

1:36set. But nonetheless, what we're going to

1:38do now is add an

1:39second category element here, we're going

1:41to put it in front of the subcategory just

1:43to give our

1:44table some partitions that we can use later

1:46on in this tutorial. Okay, now we're pretty

1:49much set up,

1:50I'm actually going to open a calculation

1:52window straight away. It's actually opened

1:54up on my

1:54other screen. So let me bring it here into

1:56view. And you can see it sort of working. I

1:58'm going to

1:59set this up so you can see it all the time

2:01whilst I've got this calculation window

2:03open. So I'll

2:04make it narrow, I'll put it on the bottom,

2:06then I'll open this right hand side panel.

2:08The reason

2:09I want to open the right hand side panel is

2:11because in there, we actually get some

2:12really

2:13useful assistance for how the window

2:15function works. So let's go in there and

2:17just type in

2:17window. And you can see earlier on, I said

2:19there's actually several functions here,

2:21they're actually all highlighted here. So

2:24essentially, the window function works for

2:26any function that's essentially an

2:28aggregation. So your averages, your counts,

2:31your max,

2:31mins, medians, sums, average, and even the

2:34standard deviation. And so you're probably

2:37wondering, well, what does window

2:39specifically mean? How does that actually

2:41work? How does that

2:42work in a visualization? How does it work

2:44in the table? First, we're going to take on

2:46a simple table

2:47like the one I've got in front of us. And

2:49we're going to just try and set up our

2:50calculation to

2:51work properly. So I'm actually going to

2:53just type this calculation out, I'm just

2:54going to type in

2:55window, sum of sales as the name of our

2:58calculation. And what we're going to do is

3:00we're

3:01just going to type in the function window

3:03sum. And I'll go down this list, and I'll

3:05select some

3:06by hitting enter. And I'll make this larger

3:08, so you can all see what I'm doing. And now

3:10that I've

3:10done this, you can see that tableau is

3:12complaining because it's expecting an

3:14expression. And so what

3:16you have to put in here is some sort of

3:17aggregation. Essentially, the window sum is

3:20going to use this

3:21aggregation as its driving mechanic, it's

3:23basically going to control how the window

3:26sum behaves. And

3:27because we've got some of sales just here

3:29in our window, what I'd like to do is put

3:32that inside of

3:33my window sum. So we can see what the

3:35window sum function or what the window

3:37function actually does

3:39to a sum of sales. Now, it's a little bit

3:41confusing, because you could, for example,

3:43use a window average. And inside of that,

3:46put sum of sales. And that will actually do

3:50an interesting

3:50calculation that I'll show you a little

3:52later. But nevertheless, let's stick to

3:53Windows sum

3:54for some of sales. And I'll just hold

3:56Command on a Mac here and drag it in. If

3:58you hold Ctrl in the

3:59windows, it will essentially copy it in.

4:01And it avoids any sort of typos that you

4:03might have.

4:04So now we have this setup, I'm just going

4:06to hit Apply. And when I do that, it

4:08essentially puts

4:08this window sum here on the left hand side.

4:11So we can use it as a calculation. Now, if

4:13you're using

4:13an older version of tableau, it might

4:15appear anywhere here on the left hand side,

4:17just

4:18basically try and make sure you find the

4:19calculation. But if you name it well, you

4:21'll be

4:21able to find it very easily. The last thing

4:23we're going to do is we're going to grab

4:25this window sum

4:26and place it inside of our table. This is

4:28like a little shorthand, you can see that

4:30it says show me

4:31here, essentially, Tableau will will

4:33suggest a way of sharing this information.

4:35So when I place it in

4:36the table, you can see that it actually

4:38does work. And we get a second section here

4:41on the right hand

4:42side for our window sum of sales. And so at

4:44this point, we've essentially set up our

4:47window sum

4:47everything is working, and it's pretty much

4:50good to go. Okay. Now, the interesting

4:52thing about this

4:53function is when we've given it sort of no

4:55instructions, it's just going to operate in

4:58this

4:58sort of standard way. And so what we can

5:00see here straight away, is that if we look

5:03on our table,

5:04especially if we look across here, you'll

5:06see that all of these values are the same.

5:08And it's pretty

5:08much the same in every single row. So what

5:11exactly is going on? Well, let's break it

5:13down. We know

5:15that the direction of the calculation is

5:17going across the table. And so what I'll do

5:19is I'll just

5:20bring out the summary tool, the summary

5:22tool is like a calculator. And essentially

5:24super handy,

5:25when you open it up, it opens it up here on

5:27the right hand side. And just in case you

5:29missed that

5:30I did that quite quickly, the summary

5:31option is just here. There's a handy thing

5:33on a Mac,

5:34actually, where you can just type in the

5:36thing you're looking for. And if you don't

5:38sort of type

5:39it badly, if you hover over the thing, it

5:41actually shows you where the function is, I

5:43find this really

5:43useful on Macs. I don't know why our

5:45Windows machines don't have this. But you

5:47can see that

5:47the summary option is just there in the

5:49worksheet section. And when you click on

5:51that, it opens it

5:52up, and it brings it over here, you can

5:54actually just grab it and drag it just

5:57underneath the

5:58filters pane here just so that you can see

6:00it in context of the view. So now that we

6:02've done this,

6:02the last thing I'm going to do to make this

6:05summary window work a bit better for us is

6:07I'm just

6:07going to drag this little gap here open a

6:09little bit wider so we can see the whole

6:11number. Then I'm

6:12going to go to tooltip and I don't care

6:14about two tips right now, I just want to

6:16show you how

6:17the window sum works. So I'm going to

6:18disable the tooltip so they don't get in

6:20the way of the demo.

6:21And so what this allows me to do is click

6:23in each of these. And you can see that the

6:25summary window

6:26changes to basically act like a calculator,

6:28it's going to basically sum up everything

6:30that I click

6:31on, and it's going to be a really handy

6:33feature. So let's now try and use this to

6:35see if this 114,000

6:38is actually some sort of aggregation that

6:40we're missing. So let's hold command, and I

6:42'll just

6:42select these top row items. And you can see

6:45that actually, when I do that, these items

6:48here

6:48equal 114,880, which coincidentally is

6:54actually what we see over here in the top

6:57right. And so we

6:59now kind of have an idea of how this is

7:01working. Now, if I do select this, and I

7:04just leave

7:04everything sort of to come back, you can

7:06see that this is actually working across

7:08the entire table.

7:09So everything is going across. And for some

7:12of you who are very astute, you will have

7:14noticed

7:14that it does indeed say here, table across.

7:17And so this gives us a hint that this is

7:19one of those

7:20functions that has some sort of direction

7:22related to it. And because we're on a table

7:24, it's working

7:26across, it goes from left to right. And the

7:28important thing to bear in mind here is

7:31that

7:32it's doing the sum of cells inside of the

7:34window function. So in essence, if I take

7:36the sum of cells

7:37and draw a box around it, what the window

7:39function is doing is that for each row, it

7:42's essentially

7:43working from left to right. And I'm trying

7:44to figure out what annotation tool to use

7:46here.

7:46So I just use this sort of manual highl

7:48ighter. So it's essentially starting here on

7:50the left,

7:51and it's totaling everything up. And then

7:53once it's done that, it's returning the

7:55value for the

7:55whole thing. So it's basically giving us

7:58114,000 on every row, it's a little like

8:01doing an

8:01LOD to the subcategory, it's essentially

8:04going to give us the same value, if we do

8:06an LOD just

8:08on subcategory, and sort of ignore

8:10everything else. Okay. So now that we've

8:13started to sort of

8:15see what's going on, let's play around with

8:17the direction a little bit more, just to

8:18see what else

8:19is going to happen here. So to do that, I

8:21actually need to just go here to the bottom

8:23left hand side,

8:24you'll see there's a little arrow in in

8:26there, it's actually a triangle, I don't

8:28know why I

8:28said an arrow. But if I click on it, you'll

8:31see that it disappears. And if I click on

8:33it again,

8:34there's a little bug where it sort of doesn

8:36't do what I want it to do. But nevertheless

8:38,

8:38I can actually go into this little drop

8:40down and you can see I get a range of

8:42directions.

8:43Now these directions are very, very common

8:45in table calculations. And once you get to

8:47know this,

8:47you really start to understand the power of

8:49what's going on, especially when you work

8:51in a sort of a

8:51table setup like this. Let's change it to

8:53table down and see what happens. So when we

8:56change it

8:56to table down, you see the number

8:58completely changes. And my guess here is

9:00that it's working

9:01from the top to the bottom, hence table

9:03down. And so actually, if I just go and do

9:06this, I'm going

9:06to hold shift, select the top one, hold

9:08shift, select the bottom one, and it didn't

9:10select all

9:10of them. So what I'm just going to do, I'm

9:12just going to select every single one

9:13manually, it

9:14doesn't want to do what I want it to do

9:16today. So we're just going to suffer a

9:17little bit. And if we

9:19add all those up, you can see that it

9:22actually equals 484247, which is indeed

9:26what we have

9:27going all the way down this row. So we're

9:29now starting to understand a bit more about

9:32what

9:33direction is doing. And we kind of have an

9:35idea of what the window function is doing,

9:38it seems to be

9:39adding everything up in a specific context.

9:42But what do we really mean by this concept

9:44of a window?

9:45Well, in order to show you that I'm

9:47actually going to need to use the function

9:49in its full form.

9:50Okay, so if I actually bring this

9:52calculation window up, so we can block a

9:54bit of the table,

9:55and I expand this further down, you can see

9:57that there's actually some additional

9:59notation that we

10:00haven't used. If I just highlight this

10:02description to you here, you can see that

10:04there's actually

10:05a way of writing this so we can actually

10:08specify the size of the window. So far, we

10:11haven't

10:11specified anything, if you actually look

10:14here on the end, we've not specified any

10:16sort of size of

10:17the window. So what I can actually do is I

10:19can go into this little space, we can type

10:22in a zero,

10:22comma, and then a zero. And this is

10:24actually going to cause us an error. Now I

10:27'm missing a

10:28parentheses here, I need to make sure that

10:30I've closed this off. So that one is being

10:32closed off

10:33there. And this window one is being closed

10:36off out here. So where am I missing a

10:40parentheses?

10:41And I think I'm actually missing a comma

10:43here. So if I just do a comma there, that

10:46should make

10:46the calculation valid. And that should be

10:49good. I had a sort of temporary mind blank

10:51there. And so

10:52now you can see that this is working

10:53properly. And so what we've done is

10:55essentially we've told

10:56Tableau to define the window with zero and

10:59zero, think of these as coordinates. And

11:01when I hit

11:02Apply, you'll see that all the numbers go

11:05back to behaving as normal. Okay. So what

11:10is the actual

11:11window? Well, there's another notation that

11:13I'm going to introduce you to. And that is

11:15the term

11:16first and last, I'm going to write these on

11:18a new line here, using a single line

11:20comment. And I'm

11:21just going to type this in first, like so.

11:25And last, like so. Now, for some of you,

11:28you might

11:29have gone, why didn't you do a multi line

11:30comment? I'm an old hack, I like to do

11:32things the old way.

11:33Okay. So nonetheless, here we are. And we

11:35've got these two terms. And what do they

11:37mean? Well,

11:38let me change this value here to last. And

11:42I'm just going to put that there. And you

11:45'll see that

11:45now my calculation is valid. So I've

11:47replaced the last zero with last. And now

11:50if we hit Apply,

11:50the numbers change again. So what is

11:53happening here? Well, there's a couple of

11:56things happening,

11:57and it's super confusing, unless I explain

11:59it really, really clearly. So let me move

12:02my table

12:02out the way. Now, for those of you who are

12:04astute, you will have realized that we

12:07still have our

12:08table down calculation working, because you

12:10'll see this over here, it's working from

12:13the top down.

12:14And so if we look at the bottom of our

12:16table, you can see the values actually add

12:19up. So 77

12:20and 77,000 add up there on both sides. So

12:23let's have a look at the next row above it.

12:25If we look

12:26at this, it says 139,414. So my hunch is

12:30that it's adding up these two columns. And

12:33so let's let's

12:33try and add these up here in Tableau. So

12:36let's click on 77 and 62. And indeed, you

12:39do get 139,000.

12:41And that is actually the value that we get

12:44onto this line. Okay. So what this

12:47calculation is

12:49doing, and I'm going to try and explain is

12:50the best possible way I can, I hope this is

12:52clear,

12:53is that we're actually describing the size

12:55of something called a window. And for the

12:59first row

12:59in our data, it's going from the current

13:02row zero. So this zero is essentially going

13:06from our current

13:06row over here. And the last row is

13:08essentially I'm going to highlight this in

13:10blue. And I'm going to

13:13use the right annotation as well. It's a

13:15little bit late here. So I'm really flag

13:17ging. But

13:17nevertheless, I'll go and highlight this

13:20with an arrow and show you the last column

13:22is over here.

13:23Okay. So zero means basically the current

13:26row and last means go all the way to the

13:29end. So for the

13:30first row, what it's going to do is it's

13:33going to go in here, and it's going to do

13:35the sum,

13:36it's going to do a window sum of the sum of

13:38cells. So it's going to grab all of these

13:40sum of cell

13:40values. So you can see, I'm just going to

13:42highlight these in a box going to grab all

13:44of these,

13:44add them up, and then it's going to return

13:48the value over here onto the top right. So

13:51that's

13:51what it's going to do on row one. Okay, now

13:54let's guess what it's going to do on row

13:55number two.

13:56Now on row number two, the current row is

13:59actually now this one here. So it's going

14:02to do this,

14:03and it's going to go to the last row again,

14:05and it's going to add them up, and it's

14:06going to

14:07return the value right there. And so if I

14:10was to do this, if I was to grab 72000, 46

14:1415, all of these,

14:17I should, if the calculator works correctly

14:21, get 464211, which is indeed what we get

14:25over there,

14:25it's just grayed out. But if I click on it,

14:27you'll see that that's the value that we're

14:29getting.

14:30So essentially, this window sum of cells is

14:32actually defining them some sort of

14:34square or window where the calculation is

14:37operating. And because it's a window sum,

14:39it's natural behaviors to aggregate

14:41everything in that window. Now I could have

14:44said window average,

14:45and what it would do instead is it would

14:47find the average of all of those values. So

14:50let's change

14:50that. But before we do, let's try and guess

14:52the number that we're going to get first.

14:54So let's go

14:54ahead and select these two here. These two,

14:58these few items here are man I cannot talk

15:01today. So

15:01let's keep going. And you'll see here that

15:04the average is 29,013. So window average,

15:08all we're

15:09going to do is change this window sum to a

15:11window average. And that's the value we

15:12should see in

15:15this row here. Okay, so we're going to

15:17change it, we're going to see what happens.

15:20So let's go here

15:21and do an average. Bear in mind, we're

15:23doing a window average of the sum of cells.

15:26So

15:26essentially, it's grabbing all the sum of

15:29cells, then it's calculating the average of

15:31those values.

15:31And the average is basically all the rows

15:34divided by all the sum of the items divided

15:37by the number

15:37of rows. And so let's hit apply. Drumroll,

15:41indeed, we did get 29,013. Okay, so we now

15:46know how the

15:47window function works. Essentially, in

15:49summary, you're defining a window. And that

15:52window is

15:53defined by essentially the coordinates that

15:55you give tableau represented here at the

15:57very end.

15:58Now, this can get very interesting, because

16:01as well as last, you can also have

16:03something called

16:04first. So let's click on this and clear

16:06this. And let's go and change this value

16:08here to first,

16:09so we can see what's going on. And now I'm

16:12going to change this last to zero. I can't

16:16spell first

16:17for the record. So that's why it's thrown

16:19an error. So let's go ahead and do that and

16:21type

16:21in first. And now you'll see that the

16:23calculation is indeed valid here at the

16:25bottom. Okay, so now

16:27let's hit apply. And now you'll see our

16:29calculation is back to normal. And I'm

16:32going to for posterity

16:33sake, return this to sum so we can track

16:35the actual change a little bit more easily,

16:38and hit apply.

16:39And now what we've done is we flipped the

16:41question the other way around. Before what

16:44we had is we had

16:46the window function starting at the top,

16:48and drawing all the way to the bottom, and

16:50doing

16:50this. And then when it went to the second

16:52row, it basically drew the window like this

16:54. And when it

16:55went to the third row, it did the window

16:57like this. What we've essentially done with

16:59this is

17:00we've flipped that the other way around.

17:02Instead, what it's doing is it's going to

17:04the bottom, okay.

17:04And it's actually drawing the table from

17:08the bottom up. So the last row here 40247

17:12is going to be

17:13adding up all the values above it. And if I

17:15go to the second last row, it's going to be

17:17basically

17:18going like this and doing that. And so it's

17:21essentially sort of using the bottom as its

17:23sort

17:23of anchoring point, if that makes sense. So

17:26if I look at this top value is 20037. And

17:29if I add 20037

17:31to 97000, that is indeed what we get over

17:34here. So it's essentially just doing

17:37exactly the same

17:38as what we were doing before, except for is

17:40doing it in the opposite order, sort of

17:42reverse the direction that we were using.

17:45And so we know what first does, and we know

17:48what last does

17:49in the context of the table or the chart

17:51that you're using first basically means the

17:54first

17:54row that you're on. And last basically

17:56means the last row that you're on. And the

17:59window is

17:59everything in between those two places.

18:02Okay. This is why if I change this first

18:05value to zero,

18:07and we just do coordinate system of zero

18:09and zero, this essentially is going to do

18:11exactly the same

18:12as the sum of cells. If I hit apply, we

18:14just have a table that looks exactly like

18:17our sum of cells,

18:18because essentially, we're defining the

18:20window in exactly the same way that Tableau

18:22already

18:22aggregates tables, essentially, the window

18:25in this case, is just start on the current

18:27row and finish

18:28on the current row. And then on the next

18:30row, it starts in the current row and

18:31finish on the

18:32current row, there's nothing sort of

18:33complex about that. So then you might be

18:35asking, well,

18:36what can we do with this? Can we change

18:38those numbers and just go crazy? Well,

18:40absolutely.

18:41Let's change this number here to one and

18:44hit apply. And now it's doing something

18:46slightly different.

18:48Because I changed the last number, anytime

18:50you change the number on the right,

18:53what you have to do is actually go to the

18:54bottom of the table, because remember, it's

18:56referencing

18:57the last row. So because I changed this

18:58number here on the right, I should actually

19:01start to

19:01look for a difference here on the bottom.

19:04So if I start with 77,000, there's nothing

19:07new here.

19:08Now, if I add 77 and 67,000, I get 139. We

19:12've actually seen this number already before

19:15. And

19:15that is indeed what we get there. Okay. Now

19:18, on the next row, we don't seem to be sort

19:20of going

19:21up in value. So let's try and see what's

19:23happening here. If I start on 62,000, and I

19:26hit 10,850,

19:28you see I get 70,000 72873, which is

19:32actually this row over here. So in essence,

19:36I've actually

19:36defined my window in this sort of tight box

19:39of two two rows. So let's just sort of try

19:42and draw that.

19:43So you can sort of see what's going on. So

19:45on the bottom row, it's going to basically

19:47just define

19:48itself as this little box here. And because

19:51there's nothing in this sort of space below

19:53,

19:53there's this sort of nothing for it to add.

19:57So just return 77,391 to this box over here

20:01on the

20:02right hand side. On the next row that

20:04changes a little bit, I'll do this in blue,

20:07so it's clearer,

20:08the window moves up. And now I'm going to

20:12essentially just draw a box around these

20:15two,

20:15it's going to add those up, and then it's

20:17going to return the value over here on the

20:21on the right

20:21hand side, okay. And that's going to work

20:23its way up. So this window is going to sort

20:25of slowly work

20:26its way up. On the next round, I'm going to

20:28leave the previous one highlighted, it's

20:29going to draw

20:30a box around these two, it's going to add

20:33those up, and then it's going to return the

20:35value, of course,

20:37over here. And that's how this is working.

20:39So this window is this concept of like an

20:42invisible window

20:43that's moving through our table, it's

20:45moving through our numbers, and it's doing

20:47the aggregation

20:47that you tell it, sum or average. Now that

20:50is different from the value or expression

20:52that's

20:53actually inside of it. So this whole time

20:54we've used some of cells, but actually the

20:56aggregation

20:57that happens in the window is defined by

21:00what's going on here. Okay, so now

21:03hopefully you start

21:04to get an idea of what this can do, you can

21:06start to kind of go crazy with this, okay.

21:08What we're

21:10going to do now is change it to table

21:11across, you're probably thinking, okay, you

21:13've absolutely

21:14murdered the point, but we really haven't,

21:16because there's more to this function than

21:17meets the eye.

21:18Let's go and change this from table across

21:22to table across, then down, okay. And so

21:26you're

21:26thinking, okay, what's actually going on

21:28here? Okay, now we're not done with this

21:30function yet,

21:31what we're going to do, we've set this

21:32completely, you can see here that it's

21:34going to table across,

21:35and you can see that the coordinates are

21:37reset back to zero and zero. And what we're

21:39going to

21:39do now is set this to table across then

21:41down, and you're probably wondering, well,

21:43what's going on

21:44here? Okay, well, let's have a look at this

21:46, because everything is set to zero, zero,

21:48the

21:48coordinates I'm looking at here, and

21:50everything is pretty much matching exactly

21:52as we thought it

21:53would. There's a table across direction,

21:55but it doesn't really matter, because the

21:57rows

21:57are pretty much just working in the windows

21:59that are the same as some of cells,

22:01essentially the

22:02cell in this particular case. If we then

22:04however change this to one, and we hit

22:07apply, then we get

22:09an error, because essentially what Tableau

22:11can't do is go for, it can't find a row

22:14that doesn't

22:15exist. This is why this is throwing an

22:16error, essentially, we've sort of hit the

22:18end of the wall,

22:19and we're asking it to do the impossible,

22:21and so that just returns a null. If we

22:23instead do this,

22:24and just set zero, and let's say we do a

22:27one on this end, and hit apply, well this

22:30works fine,

22:30because essentially we're asking it to move

22:32in a direction that actually currently

22:34works.

22:34So let's try and decipher what's going on

22:37here. Let's hit this, we're going to table

22:40across then

22:40down, okay, and so let's have a look and

22:42see if we can sort of make sense of what's

22:44going on. So

22:46if I grab my box here, and I look at this

22:48table, you're probably thinking, okay,

22:50where is the aggregation starting? And what

22:53I tend to do is I tend to typically look

22:55for a number

22:56that makes a lot of sense for me, and the

22:58first one that makes sense is actually here

23:01on the

23:01bottom right hand side, and this is how I

23:03sort of understood Tableau, this is how I

23:04've learned

23:05Tableau. If I don't know something, I try

23:07and find something that makes sense, so

23:09these two numbers

23:10are the same. Now the number above it and

23:13the number to the left of it don't look the

23:15same at

23:16all, and so what I'm going to do is I'm

23:18going to sort of take a guess, and I'm

23:20going to assume

23:21that actually these two numbers here

23:23actually add up to 184,000. So you can see

23:27this is 184,000,

23:29and that is actually what we get in this

23:31row. So I've now got a hint as to what's

23:33going on,

23:34so what I'm going to do is I'm just going

23:36to sort of keep going in that direction

23:38until I see what

23:39else is happening. And so if I go across

23:42these two here at 184,000, these two here

23:45147,000, that's

23:47what I see there, these two here have 145,

23:50000, that's what I see there, and then I'm a

23:53little

23:54bit stuck because I don't know where to go

23:56next. So let's try this, let's go 77 and 62

23:59,000, 139,000,

24:00that's not what we're seeing here or here,

24:02so that's not correct. Well the only other

24:05place it

24:06can go is to go to the row before it. So

24:08let's click on that, and that, and you see

24:11that it gets

24:12120,935. And that is actually what we get

24:18in this particular cell. So it's kind of

24:22doing this,

24:23if I sort of try and draw it on screen, it

24:25's sort of drawing these sort of small

24:27squares,

24:27I'll do this, I'll do this, I'll do this,

24:29that's the first one. And then this is the

24:32second one.

24:33And then the third one is sort of spread

24:36across like two boxes, it's sort of this

24:38one, and that

24:39one. And then once it gets there, it kind

24:42of carries on doing its own thing. So this

24:44is

24:45actually using a table structure called

24:47across and then down. And because of the

24:49way we've defined

24:50the window, it's actually doing it from the

24:52bottom right hand corner to the very top

24:53left hand side.

24:54And so if I actually was to add this number

24:57and this number together, you'd get 58,580.

25:01So then these two essentially represent

25:04this particular record here. So the way

25:08this is

25:08working, it's sort of fascinating, the

25:11window sum can basically do some really

25:13weird direction in

25:14terms of the maths that it's doing. And it

25:17's all defined by what you pick. So if I was

25:19to go in

25:19here, and change this instead of table

25:22across, then down, I could just paint

25:24across then down.

25:25And then you get this. And then you're

25:27thinking, oh my word, what is happening

25:29here? Well, there

25:31is actually a logic to this. What has

25:33essentially happened is that there are some

25:35categories here

25:36that don't have any particular sales in the

25:39particular section that we're looking at.

25:41So

25:42for example, furniture doesn't sell

25:44anything when it comes to accessories,

25:46appliances, art and

25:47binders, you can only have furniture sales

25:50in bookcases, chairs and furnishings. And

25:53so when

25:54we actually set that to paint across and

25:56down, you sort of forcing Tableau to do

25:58some maths in

25:59a direction that it really doesn't want to

26:01do it in. And there are no sales in that

26:03particular

26:03cell. And so you kind of get this funny

26:05behavior, and you never really want this

26:07because this makes

26:08no sense to anyone is that you can see let

26:10's actually go to the bottom and let's just

26:12start

26:13here, let's go to the very bottom where we

26:15were before. So technology, the most bottom

26:17right value

26:18you can have is 105,341. And that is

26:22actually indeed what we have here. And we

26:24can actually

26:25see that working properly. Okay. Now, if we

26:28go to the next row, and we get these two

26:30together,

26:31the values that we get for these two should

26:34be 184,303. And so everything is still

26:37working.

26:37That's what this is saying. But when we get

26:40to 77,000, you can see here, there is no

26:42row above

26:43it that we can use. And essentially, it has

26:45to go to the row above it, because I forced

26:47it to use the

26:48pain down. And so when it adds 77,391 to

26:52nothing, you get 77,391. But when it goes

26:56to this cell,

26:57and it adds this cell, well, the answer is

26:59nothing. And then it will keep trotting

27:01down

27:02through this particular setup until it gets

27:04the paper over here. And then the next

27:06thing it will

27:06do is it will add this one to this one. And

27:08it will represent that over here. And so

27:11essentially,

27:12it's still doing what we asked it to do.

27:14But because we're forcing it to go through

27:16the pain,

27:17it's creating data where there wasn't any

27:19data before, it's almost like it's done a

27:22secret

27:22scaffold. And this has brought back all the

27:24other sort of data items that we're sort of

27:26interested

27:27not interested in. So if we go back here, I

27:29shouldn't really use pain across then down

27:32in

27:32this particular setup, it's a really,

27:33really bad idea. So let's go back to table

27:35across and down,

27:36and then you can see everything else

27:38disappears. And so the main thing you want

27:40to take away from

27:41this is that look, the windows sum function

27:43is a very, very powerful function, the

27:46window itself

27:47is defined by the coordinates that you give

27:49a tableau in these two positions, you can

27:52either

27:52write those coordinates in the way that I

27:54've got on screen. So you can either write

27:56it like zero

27:57like this. And I don't know why I used o

28:00zero comma zero, you could write it like

28:04this, you can

28:05do first, like this comma zero, okay, you

28:08can also do offset. So you can also write

28:11it like this first

28:14plus one, comma zero, okay, so you can

28:17write it in so many different formats. And

28:21there's simply

28:21not enough time to go through all of them

28:23in this video, I just wanted to give a very

28:25basic

28:26introduction into exactly what the window

28:28function is doing. So hopefully, you can

28:30sort of breach

28:31that gap and have a play for yourself and

28:33figure out how it works or how you can use

28:35it in a

28:35calculation. Now, I'll probably do a second

28:38video on this because we definitely not

28:40done enough here

28:41and this video has gotten a bit too long.

28:44So I'm going to call this window functions

28:46part one. And

28:47in part two, we'll do something a little

28:49bit more complex, where we'll basically use

28:51this in a line

28:52chart. And we'll also try and use some

28:55aggregations that actually do the same

28:57thing. But you don't

28:59realize that do the same thing, I'll give

29:01you a sneak peek of what I mean by that,

29:02just so that

29:03I can whet your appetite for video two, I'm

29:05going to hit OK here, and open up a new

29:07sheet, we're

29:08going to build exactly the same thing. I'm

29:10going to put sales on text, I'm going to

29:12put subcategory

29:13and category over here, I could have

29:15brought category and expanded it out. I

29:17know that,

29:17but I didn't. So that's that's what being

29:20an old hack is about. And so here we are,

29:22we've got all

29:23the numbers that we set out. Now, what you

29:25can actually do sometimes is you can go

29:27into this

29:27summer sales, you can do a quick table

29:30calculation, and you can do a moving

29:32average. And so it

29:33actually does a moving average in the

29:35direction of the maths. So in this

29:37particular case, it's using

29:39table across, it's going from left to right

29:42. And if I go in and edit that table average

29:44, let's say

29:45edit table calculation, you'll see that I

29:46actually get a little highlighter showing

29:48me the direction

29:49in which it's working. And you can see it's

29:51average, and it's using the previous two

29:53values

29:53and the next zero values. Okay. And so that

29:56's actually an interesting description,

29:58because that

29:58sounds awfully familiar. And the reason it

30:01is familiar is because that is actually

30:03exactly

30:03what's going on when you use table

30:05calculation. If I actually expand this, and

30:07I just double click,

30:08you'll see that Tableau is actually writing

30:11window functions for you secretly, when you

30:13do

30:14all these functions, like running totals,

30:16moving averages, and everything else, it's

30:19actually doing

30:20the maths for you. So you don't have to

30:22figure out how to do these things yourself.

30:24So we're going to

30:24cover more of this in the next video and

30:26sort of break that down. But that function

30:28right there is

30:29essentially doing a moving average. So

30:31looking at the previous two values and the

30:33next zero values,

30:34that's all it's doing, you could of course,

30:36change that and say, give it a window of

30:38three items,

30:39the previous two and the next one value,

30:41that's actually four items, I can't count.

30:43But then if

30:43I hit enter, you'll see that that also

30:45works. And it becomes a window average, we

30:47've sort of broken

30:48it free from its automatically created

30:51setup. And it's gone back to being a

30:53calculation that we can

30:54use. Pro tip, we can also drag it in here

30:56and turn it into a calculation. So we can

30:59call it window

31:00average from table calculation. Okay, so

31:05man, I've rumbled a little bit, we've sort

31:08of gone around

31:09the block here, we're not done with the

31:11window function, I feel like it's an

31:12absolute beast of

31:13a function to understand, not least because

31:15every single aggregation can be used with

31:18the window

31:18function. And you can also put other

31:21aggregation inside of it. So you could do

31:23an average inside

31:24and then find out the standard window

31:26deviation of that particular thing. It gets

31:28complicated. So

31:29this is part one, this will have to do for

31:31now, it's getting late, and I'm gonna have

31:33to stop

31:33recording. But in the next video, we'll

31:35obviously jump into more of this. And we'll

31:37try and build

31:37some actual charts and see how the window

31:40function works in real charts. Alright,

31:42thanks for watching,

31:43and I'll catch you in the next one.