Analyzing data with Quick table calculations

Tableau is one of the most widely used data visualization tool to get insights from big amount of data. Tableau has many advanced features and quick table calculations is one of them.

Quick table calculations are set of inbuilt calculations that we can apply at the dataset level. These calculations allow to quickly apply a common table calculations into visualization without writing any code.

Following are the predefined quick table calculations available in Tableau.

Let me demonstrate this using "Global superstore" dataset.

Global superstore is a online retailer dataset contains 51294 rows and 24 columns. Product categories in this dataset include furniture, office supplies and technology.

Lets see about the running total and rank from the list of quick table calculations.

1. Running total of sales by quarter and restart the total by each category:
   

Load the dataset in Tableau and drag the order date to columns and category to rows as shown below.

In order to get the sales for each quarter by category drag order date next to category in rows so it can be nested to the right of category as shown and change the pill to show quarte. Click on the pill and select quarter.

Now drag sales to text to show the values.

Here we can perform running total. Running totals can be done for the table, pane and cell.

Running total Table across - Calculates the cumulative total across the table

Running total Table down - Calculates the cumulative total down the table

Running total for Pane(down) : Pane will be considered as shown. Each category will become pane here. So pane down is calculating running total for each year for each category.

Coming back to our analysis we are doing running total for pane down.

Click on the sales pill - Quick table calculation- Running total as shown below.

Again click on sales pill - compute using - Pane down.

Here is what we get when you do the above steps.

You can see its calculating cumulative total for all the quarters as per year for each category and resetting the value for each category.

If you want to sanity check you can bring the sales to check with running totals. Here is how you do that.

So to bring sales into the view double click on sales. Here is how it shows.

It brings the measure values to the marks card measure names to the filter and columns.

You can see running totals and sales are shown side by side. But if you want to see them nested within the year you can move measure names in column to the right side of the order date as shown below.

So you can see sales and running sales beside each other per year. From this you can answer few questions.

What were the running totals for technology in quarter 2 and quarter 3?

Quick table Calculations using Rank:

Lets calculate the top 10 products in each market using Rank.

You might be thinking of Top N filter to do this. But with big data it can show Top N for the overall sales but not for each market. Top N filter do not nest and not affected by dimension filters by default.

Lets do this using Rank.

Drag subcategory to rows and sales to columns. This is going to give bar chart as shown.

From the chart you can say phones are the best products and labels are least sold.

Now click on the sales pill -Quick table calculation - Rank. You can see below the axis changes from sales to Rank of sales.

This is kind of misleading by looking at the chart. so lets label this with ranks.

Drag sales pill with quick table calculations to label in marks card and bring sales from data pane to columns.

Below show after dragging sales with quick table calculations to label .

After bringing sales to columns.

Above process will give the label to the rank. So there is another way of ranking to show as header.

This can be done by dragging sales pill with quick table calculations beside subcategory and change it to discrete. By doing this the pill color changes from green to blue and gives the header beside the subcategory as shown below. Now drag sales from data pane to columns to show bar chart.

Now if you want to calculate top 5 products in each market. Drag market and product to rows and sales to columns.

Click on sales pill - Quick Table calculation.

Again click on sales pill - compute using pane down since we have 4 different markets and we want ranks for each market. By default it takes Table down but we need to select pane down to get the ranks for each market.

So now by following one of the 2 procedures we discussed before to label the ranks.

Either label or header.

So dragging the sales with quick table calculations to label and sales to columns will give the labels for ranks for different products in each market. Since we selected pane down it is going to reset for each market.

But this did not solve our problem as we have so many products we cannot put them on the dashboard. so we want to see only top 5 or 10 products in each market.

Guess what we can use table calculation as a filter. Its called a table calc filter. Table calculations are done last in the order of operations.

So copy sales pill with quick table calculations from marks card to filter. This can be done by clicking control and drag it to filter. By doing this you will get this msg.

So you can set the values from 1 to 5 and click ok.

From the above you can see top 5 products in each market. Rank table calculations being used as a filter gets you around to answer very common question in the organizations in todays world.

Conclusion:

• Quick table calculations are special set of calculations in Tableau that apply to the values that are currently in your view.

• Within the table calculations Tableau allows to select the type of calculation and method of compute like Table(cross), Table(Down) etc..

• Table calculations are the last ones in the Tableau order of operations.

Thank you for your time.