Everything Table Calculations

by | Feb 22, 2020 | Alex Taylor-Jackson | 1 comment

INTRO

In this blog, I will be describing all things to do with Table Calculations. The pretence behind this blog is to help with a teaching session I will give soon about Table Calculations.

So, what are Table Calculations? Directly from Tableau help, “Table Calculations are a special type of calculated field that computes on the local data in Tableau. They are calculated based on what is currently in the visualisation and do not consider any measures or dimensions that are filtered out of the visualisation”

This means that they are a way of manipulating data on Tableau, for example, if you wanted a running sum of sales for a year then this could be done with Tables Calculations. Below is Picture of where Table Calculations can be found in the calculated field menu.

I have grouped the Table Calculations into different groups, as they all perform different tasks. For example, in Blue, we see the position-based Table Calculations. This work based on where the value is located. In red we see the Running expressions. In Brown the Scripts. In purple, the other Table Calculations. Lastly in Black, the Window Table Calculations. Below I give examples of when to use these.

EXPRESSIONS

position-based Expressions

  • INDEX – Returns the Position of the Current row in a partition (group). Linked to FIRST, as it is derived from the first index position subtract row position. So, the 10th Customer, in this case, would have a value of 10.
  • FIRST – Returns the positional difference from the first row in a partition (group). So 10th Customer would have a difference of -9. This Calculation is useful with Visualisations that involve Calendars or Planning.
  • LAST – Returns the positional value from the Last value. Using Superstore Sample data found on Tableau, I get the first customer as having a “LAST” value of 792.
  • LOOKUP – This Calculation can be used in conjunction with Table Calculations like FIRST, for example, ZN(SUM([Profit])) – LOOKUP(ZN(SUM([Profit])), FIRST ()) gives the difference in profit from the first value. This Calculation does not have to be memorised as it can be made using a quick-Table Calculation, these are made by dragging your profit variable to the marks section right clicking selecting quick Table Calculations and selecting whatever you would like. Once you have done that you can create a Calculation and drag the quick Tabled “Green Pill” into the Calculation box. Rename it to whatever you would like.
  • PREVIOUS_VALUE – This Calculation can be used to get the running sum. We can use a Calculation like SUM([Profit]) + PREVIOUS_VALUE (0). This Method is a bit more intuitive than Lookup.
  • RANK – The Rank Calculation uses positions like before but based on values, for example having the largest profit, would result in a rank of 1, e.g. 1st. RANK(SUM([Profit]),’ASC’) would give rank in ascending The default behaviour of RANK is very important as any two or more same values would get the same rank, so the very possible outcome could be 1st, 2nd, 2nd, 4th – Skipping 3rd place completely.
    • RANK_DENSE – This rank Calculation instead allows third place after, so 1st, 2nd,2nd,3rd
    • RANK_MODIFIED – This rank Calculation instead feels that if two values are identical, that the rank after is best to use, so 1st, 3rd, 3rd, 4th – skipping 2nd
    • RANK_PERCENTILE – This rank behaves similarly to RANK_DENSE as the positions are divided by the end rank, for example, 1st, 2nd,2nd,3rd would give a result of 0, 0.67, 0.67, and 1. This is because 0/3 here is 0 , 2/3 is 0.67 and 3/3 = 1. Why would this ever be useful? Well, the rank that yields 0.5 e.g. 50% would be the median value.
    • RANK_UNIQUE – This Rank gives a value of 1st,2nd, 3rd, 4th even if the values are identical.

Running Expressions

  • RUNNING_AVG – Gives the running average of expression, so that a clustered line chart with many peaks becomes smoothed out. I personally prefer the quick Table Calculation of a moving average as I can control more aspects of how many periods the value is averaged by. With enough previous values to average the expression, the running average and the moving average become the same.
  • RUNNING_COUNT – This Calculation is essentially a running sum of the count of values, useful for showing how the count changes over time.
  • RUNNING_MAX – This Calculation returns the max value over time, so as a line chart the max value is updating on whatever level of granularity. As time moves on the value will remain as a straight line until a new maximum value appears.
  • RUNNING_MIN – Does the same thing as RUNNING_MAX except in reverse, so the minimum value is updated over time.
  • RUNNING_SUM – Returns the running sum from the first row to the current row

Script Expressions

  • SCRIPT_BOOL – The Script Table Calculations use R and Python programming language, without downloading an external package, these will not work. BOOL implies a true-false statement, for example, SCRIPT_BOOL (“. argl>0”, SUM([Profit])) would return true if profit was above 0. For Python it would be SCRIPT_BOOL (“return [x > 0 for x in_argl]”, SUM([Profit]))
  • SCRIPT_INT – Is like other SCRIPT expressions but returns an integer
  • SCRIPT_REAL – Is like other SCRIPT expressions but returns a numeric value
  • SCRIPT_STR – Is like other SCRIPT expressions but returns a string

Other Expressions

  • SIZE – This returns the number of rows in a partition, useful in correlation matrices. Quite like “Number of Records”
  • TOTAL – Works in a similar way to Level of Detail Calculations, for example, if you wanted total average profit over three categories then TOTAL(AVG([Profit])) would give you the average across all rows, whereas AVG([Profit]) would yield average profit per category.

Window Expressions

  • WINDOW_ – The below expression with WINDOW_ appended in front of them (e.g. WINDOW_AVG) return the value of the expression in the whole window. The expressions used below can be found in the AGGREGATE section of calculated fields. These can be useful when your Level of Detail Calculations won’t allow you to use the Calculations.
    • AVG
    • CORR
    • COUNT
    • COVAR
    • COVARP
    • MAX
    • MEDIAN
    • MIN
    • PERCENTILE
    • STDEV
    • STDEVP
    • SUM
    • VAR
    • VARP

In my opinion, there will be specific cases when to use some of these Table Calculations. However, some of my personal favourites are:

  • WINDOW_AVG – with WINDOW_AVG(SUM([Profit]), -2, 0)
  • LOOKUP – with (ZN(SUM([Profit])) – LOOKUP(ZN(SUM([Profit])), -1)) / ABS(LOOKUP(ZN(SUM([Profit])), -1))
  • RANK_DENSE – with RANK_DENSE(SUM([Profit]))

QUICK TABLE CALCULATIONS

Very briefly there are a few different calculations that are “pre-built” into Tableau. By right-clicking a measure mark pill, a few options will appear:

  • Running Total
  • Difference
  • Percent Difference
  • Percent of Total
  • Rank
  • Percentile
  • Moving Average
  • YTD Total
  • Compound Growth Rate
  • Year over Year Growth
  • YTD Growth

I hope I was able to help with any brooding questions or clarifications that people may have had or wanted before reading this blog.

Alex Taylor-Jackson
Author: Alex Taylor-Jackson

1 Comment