What is LOOKUP Function in Tableau?
The LOOKUP function in Tableau tool, which is used to look up a value in previous or subsequent row in a table or partition. The LOOKUP in Tableau function can be used to calculate running totals, moving averages, and other types of calculations that require access to previous or subsequent values in a table or partition.
Here is the syntax of the LOOKUP() function in Tableau tool:
LOOKUP(expression, offset)
Understand the function arguments/expression:
- Expression is the expression to be evaluated
- Offset is the number of rows before or after the current row to use for the lookup. The offset value can be positive or negative.
What is the ZN lookup function in Tableau?
There is two different function combines to make ZN Lookup function, ZN function used to replace Null vales with Zero and LOOKUP used to look up a value in a previous or subsequent row in a table, so what happen when there is any Null vales in field that replace by Zero with the help of ZN function.
Lets take example below, here ZN replace the Null values with zero if there is Null on Sum level.
LOOKUP(ZN(SUM([Sales]), -1)))
What is lookup table vs lookup?
Below are the main difference between Lookup table and Lookup
| Lookup Table | Lookup Function |
| Lookup table is the method where you can perform task at table level meaning you can show differences and calculation only numbers of rows available in particular table and chart view. | Lookup is the Function or formula used to calculate with offset to in calculate field which can be used any columns and vies withing the dashboard. |
| This is the manually applied method or calculation on a particular table | This is the Formula based calculation |
| Its perform computations on values in a table | Its perform computations on row levels of data source |
Key features of the LOOKUP() function:
- Can be used to look up values in previous or subsequent rows in a table or partition
- Can be used to calculate running totals, moving averages, and other types of calculations that require access to previous or subsequent values in a table or partition
- It can be used with dimensions or measures
- It can be used with discrete or continuous fields
- It can be used in calculated fields, table calculations, and level of detail expressions
Lets try to understand more with some examples of the LOOKUP() function in Tableau tool with syntax:
Example #1: Calculate Moving Average in Tableau using Lookup
Calculate the moving average of last 3 years of [Total Cost] by looking up the average of the previous 3 years values of [Total Cost] and adding it to the current value.
You can use below Calculated formula with Lookup in Tableau.
(SUM(LOOKUP(SUM([Sales]), -1)) + SUM(LOOKUP(SUM([Sales]), -2)) + SUM(LOOKUP(SUM([Sales]), -3)) + SUM([Sales])) / 4
The out put will be look like as show below screenshot. The third column shows how the average is calculating its taking average of last 4 years.
Example #2: Calculate Percentage change in Tableau using Lookup
Calculate the percent change in sales by looking up the previous value of sales and calculating the percent difference. In below formula it is calculated with the help of Lookup function in Tableau
(SUM([Sales]) - LOOKUP(SUM([Sales]), -1)) / LOOKUP(SUM([Sales]), -1)
The result will look like as shown below image
The result will show the percentage in decimal integer, you can change this into percentage by following below steps
- Right click on Calculated filed from “measures values” are and then click on Format option as shown below image
- Now click on Pane option > the numbers > the percentage and select decimal places for percentage, as shown below image
Example #3: Calculate difference between current and previous values using Tableau Lookup Function
Calculate the difference between the current value of sales and the average of the previous three values of sales. Here you need to use average lookup formula and simple SUM formula to calculate these see the below calculated formula.
SUM([Sales]) - (SUM(LOOKUP(SUM([Sales]), -1)) + SUM(LOOKUP(SUM([Sales]), -2)) + SUM(LOOKUP(SUM([Sales]), -3))) / 3
Below is the output image , which calculate difference between current values and the average of the previous three values of sales
These examples demonstrate how the LOOKUP() function can be used to look up values in previous or subsequent rows in a table or partition, and how it can be used to calculate difference, averages and other types of calculations. The LOOKUP in Tableau is a powerful tool for performing complex calculations in Tableau.