#### Transcripts

1. Part 5 Table Calculations: Welcome to Section five. We will look at table calculations in this section. Table calculations air one of the most powerful features and tableau. They enable solutions that really couldn't be achieved in any other way. They make it possible to use the data that isn't structured well and still get quick results without waiting for someone to fix the data. At Source, they make it possible to compare and perform calculations on aggregate values across rows of resulting tables. They open incredible possibilities for analysis and creative approaches to solving problems . Tabal calculations ranging complexity from incredibly easy to create a couple of clicks which will see soon to extremely complex, requiring an understand of address and partitioning, and dated that identification. We'll start off simply moved towards complexity. In this chapter, the goal is to gain a solid foundation to create and used table calculations, understand how they work and to see some examples of how they could be used. We'll cover these topics. Overview a table calculations, quick table calculations, scope and direction. Addressing at partitioning, custom table calculations and practical examples. Most of examples will use a superstore extract data set that we've been using so we can open up workbook for Part five and let's get started on over the O table calculations should should be probably the first place we start. Um, let's do a quick and easy table calculation, and then we'll get to some of the more details. So let's say we want to look at sales by departments God's word category, and they will blame department soon. So and now we want to figure out the percentage of the total sale. It's really the critic quick table calculation and use percentage of total. So what this does, it creates another look. We want the sales in there, too, and then we want to sales first. All right, so this shows you the percentage of the total of the sales. So that's a really quick and easy way to look this at this table calculation. Creating and editing table calculations there are is pretty simple and easy there. You can use the quick table calculations, and then you can get more complex by adding table calculations. Let's take a look at the quick table calculation that we made. If you go and at a table calculations, it brings up that table calculation window. Quick table calculations are pretty defined table calculations that can be applied to any field used as a measure in the views. These calculations include common and useful calculations. Such a running total, which is at the very top running total difference from percent of total, which we did right now, and rank percentile moving average year today. Total compound growth were a year where your growth a year today. Growth. All these can be you created using coat table calculations. So we just created type of calculation that shows the percentage of total sales. But let's say we want to look at it by department so we can pull in the department. And these Taylor calculations didn't change. However, let's say we wanted to see whether the department how much what percent of the category of sales did that department have? We can do that by changing the quick table calculations so we can edit the table calculations and a set of using table down or table. We should use the pain. So what this does it looks at this summary and gives you a percentage of the total sales at the pain level. Super powerful, super useful that you could do this on a day to day basis. So that's a quick introduction. Table calculations, and we'll review scope and directions and look at significantly more complex table calculations in the next video.
2. Scope and Direction: Now we create a simple table calculations, but let's look at working with scope and direction. We won't use. This tab will go to scope and direction tab and look at how scoping direction is used. These terms are used to describe how a table calculation is computed relative to the table . When the table calculation is relative, rearranged the fields and the view will not change the scope and direction. Let's see what scope in Russian our scope is the defines of boundaries within which a given table calculation can reference other values. Direction is the direction how the table calculation moves within the scope. You've already seen table calculation being used using the table down option in the last tab. Now we're gonna use the running total and explore how the scope and direction is used to define the scope and direction for a table calculations. Using a drop down menu, the field you can use the compute function, so let's let's go in here and start compute using. So right now we're using the table across. We can change that from table across the table down so you can see it went from numbering one through 12 across, which is a running total to numbering one through 12 going down for each pain for each for each row. Column for each column. You could do this at the pain level you could do is have a cell level. You could do it to the table level, so let's get in there and start looking at that and save computing using weaken. Edit the table calculation and it gives us a more robust view. So we had a table across. Now we can look at the table down when using table down the end index increments from the entire table down. Now let's look a table across. Then doubt this increments the running total across the table and then steps down to continue the increment across repeated field all the way across the entire table. We can do this down, then across, so first it goes down and then it starts in the top again to get all the way back to 1 92 we can do on Lee the pain across. This defines the boundary for the running total and causes that totaled increment across the table until it reaches the pain boundary at which the point. They're running Total restart, so you can see it goes 123 Stop! 123 Stop! 123 Stop! So this is a cross. We can do the same thing down and then we could do down. We can do pain across on then down. So let's say we wanted 123456789 tone. So we wanted each box to have a total of 12. So if you select across then down you will see 123 And for each pain, that's what happens. And then lastly, we can look at the cell level which will just show what? Because that stops. The scope stops at the cell level, so scope and direction worked with any table calculation. Consider how a running total or percent difference would be calculated using the same movement and boundaries shown in examples. Examples and the list don't cover every possible combination of scoping direction right up to the table. Keep experimenting with different options. See how you feel. See if you feel comfortable, house scope and direction are applied. Next we will look at addressing and partitioning, which are these two, which is this box down here, and these two options out here
3. Addressing and Partitioning: Let's review ad dressing and partitioning address ING and petitioning are very similar to scope and direction, but are most often used to describe how table calculations are computed with absolute reference to certain fields. In the view, without addressing and petitioning, you define which dimension in the view defines that partition or the scope and which dimension defines the address ing, which is the direction using the address ing a petition and gives you much finer control because your table calculations are no longer relative to the table layout and you have many more options for fine tuning the scope and direction and the order of the calculations . So let's take a look by changing the compute using across them down. We can see in 2012 Q 2 office supplies for the East is number 14. If we swap rows and columns, it becomes number 18 so the number 14 is all the way over here. Now it's 2015 Q 2 furniture, so you want to be able to use this as a reference to anything so we can create a mechanism to make sure that the address ING stays. The office supplies East Q 2 2012 stays. Where we're right there as days is number 14. You can do that by choosing specific dimension. We'll start with that. So if you choose specific dimension and then you see number 14 here, you flip it and you can see office supplies still stays number 14 for Q 2 2012 So whether or not the no matter how you're using the computing the address ing space, it keeps your relative calculations the same. So now you're able to use that in a much more robust manner. This the advance and addressing a partitioning will really make it easier for you to do many more advanced calculations and keep consistency across your analytics. So in the next section will start to look at advanced table calculations and real world examples of how to use them.
4. Advanced Table Calculations: Let's talk about advanced table calculations like meta table functions before we move on to some practical examples, let's consider advanced table calculations. Advance in this case simply means that code is written. Instead of using a quick table calculation option, you can see a list of available table calculations functions by creating a new calculation and selecting table calculation from the dock dropped out under functions. So let's do that. We go to create calculated field and under here we want to look at table calculations, so these are all the functions available. Under advanced table calculations, you can think of table calculations broken down into several categories. In each of the examples will go to compute using and then category, which means department is the put the partition. The various advanced table calculations include meta table functions, so those include index first last size, so you'll see those here first gives you the offset from the first row in the partition. So let's take a look at that that's exit out of this first. So here's the first column and gives you the first offset from the first row in the partition. So the first row in each partition is zero, all right, and then the last gives you the offset for the last row so you can see that here in the last column, um index first and last are all affected by scope and partition and direction. Address ing while size gives the same result at each address of the partition, no matter what your Russian is specified, so I can see that the size here is all the same. Look up and previous values are another table functionality, so let's look at those look up in previous values. The first of these two functions give you the ability to reference values in the other rows , while the second gives you the ability to carry forward values. Noticed their direction is very important for these two functions. So if you look at the previous values, this just shows books. Now it adds books, chair and shares. This has books, chairs and for office furnishings. Now this has books, chairs, office furnishings and tables Out. The look up only gives a previous row, so there's no previous rule above bookcases, so that's why it's a no. The previous row from chairs is bookcases, so if you change If you change the sort order, these will change. So keep that in mind when using the's dysfunctionality Next will notice. We'll look at, um, let's explore the look up calculation. So if you change the sort, you can manually sorted or but you can choose a sort of based on the data order. So let's change, move, move the computers and peripherals all the way to the second to the top and you noticed that didn't do anything. Nothing changed. So you can see computer purples are still, um, in the same order there in. So the sort order really doesn't impact these whole lot. If we take a look at category and we sort the category from ascending to descending, you can see that these values did change because we're looking at the previous values. So that's how look up and previous values work. Now, in the next videos, we're going to start looking at the practical examples of using this thes advanced table functions
5. Moving Averages: we're back to the original sheet mirror we were using. And in this video we will review moving averages, um, so smooth out variations in charts you can use moving averages. The following is ah, example of how to use moving averages toe. Identify trends. So let's look, remove department and category and look at sales by month based on the order dates. And we want to look at that by month, and we want to see this in a line chart. So there's a lot of ups and downs. It's hard. This a lot of noise going on here. So we want to see a line that smooths out some of this noise. So that's pullin sales again and make it a quick calculation of a moving average. Um, let's use dual axes, and this shows you that the moving average is a little bit smoother. You can see despite right here is only one peak. So if we want to edit this moving average, we can actually edit the table calculations and see. Right now, there's only use the last two. So if we use the last six still becoming smoother and then if use a full year if used 12 months rolling average. This shows you the average sales per month of the last 12 months. That's what each point is, and you can see the trend is clearly going up. But at the very end, it starts going down after, um this November 2014 is calculated in. So that's approved practical example of how to use moving averages. Be careful when using these moving averages because they will smooth out, allow the noise. But at the same time, you have to make sure you know what data is available. So you're not making poor decisions based on incorrect data.
6. Rank: and this video, we're going to use rank, which is a little bit, 11 more advanced function, and we're gonna see some ranking with higher levels. Let's start out by looking at sales by. So that's pulling the order total. And we want the sale, not by month. Sorry we wanted by department by region Department by regions. So you can see this is sorted based on the alphabetical order. I want to see this sorted based on the, um, I want each department bait sorted based on the sales for that department. So I want the region sort of based on the sales for the department. So let's see if we can sort this based on the sale of some of the sales. You could say it did did just that, and we wanted descending. Let's go to go back and change that to descend A. So you can see Central is always at the top because, as a whole, that has the most sales. However, if you're looking office supplies, the East has more sales in the central region. Teoh. To resolve that issue, we can create a calculated field that has rank, so I've already created it Let's take a look at it. All it does is takes a rank of the some of the sales, and you can pull that in. Drop that in right between. We won't make a discreet field, and we want that to be right between the department and region. What this will do it it'll create. We'll make the rank based on the individual regions. So you can see here. The East is at the top now, which is exactly what we wanted. And it's all sorted based on the region sales for each department. That's one way to use a limit of advanced ranking functionality, and the next video we'll look at late filtering.
7. Late Filtering: so we reviewed how to use franking with special nationality. Teoh rank at a higher level. Now we're look at late filtering. This is a good example to use for when you want to use a percentage of sales. So let's do that. Let's make this a percentage of total. Now you have a percentage of total, and we only want to filter this for the furniture. And we don't want to show office supplies in technology. So if we change that to furniture only, it changes the percentage of the rankings. We don't want that. We want the percentage to stay the same. So we want central to be 9.5 and used to be 9.6, the West to be 10 and the South to be 6.1. So there's, um, a feature called late filtering. Since table calculations are performed after the aggregate data, aggregated data is returned to the cash. The filter on the department has already been evaluated at the data source, and the aggregate rose don't include any departments excluded by the filter. That's a percentage of total is going to be, you know, inaccurate. So if we actually remove region and just use French. Or again, you're going to see that it's gonna be 100% which is not which. Not fun shall we want. We want to say 35.5%. Or you want the regions to stay at as they are so we can create a filled of a field called department late filtering, using the look up and the A T T. R function. So let's take a look at what that looks like. So I've already carried that function you had. Look up and you choose the department and you set it at zero. So if the expression in the offset is what you're sitting here and then the a t t a t T r function returns the value of a given expression, if only has a single value so best of the functional we want to use. If we drop this into the filters bucket, it will give us the option to filter on furniture, choose furniture, and you could notice it's a 9.6 10.1 as it waas before we added the filter. Now, if you remove the region, you can see that this is at 35.5% instead of 100% that we saw earlier. So you might have noticed that we the a t t r function use remember that the table calculations require agri arguments. A tee tee are short for attributes. It's a special aggregation that returns the value of of a field. If there's Onley, a single value of the field present for a given level of detail. To understand this experiment with a view that has both department and category rose using the drop down menu on the active fields in the view change Category two attributes. So let's try that right now we add that category back in, and if we change that to an attribute and seven dimension, you can see it drops it altogether because of the fact that we have the attributes function in there. So just be careful when using the attribute function in your calculations, so that summarizes the advanced functionality with regards to looking at rows and dimensions, we've covered a lot of concepts around in table calculation. In this chapter, you make you have a solid foundation to understand anything from quick table calculations, toe advance table calculations, the practical examples recovered barely scratched the surface of what is possible. So imagine being able to combine all these different functionality into a larger dashboard . The kind of problems that we can solve and the diversity of questions that can be answered are almost limitless. Now we'll turn our attention to some a lighter topic, something completely different. It's very, very important to understand formatting and design in the next chapter in the next Chapter four, Manning and design is super important to get your story across to your users, so let's close out this part and jump in to formatting.