Tableau Lookup

What is Tableau Lookup Function?

An essential aspect of data analysis is to compare the current value with previous values and find the apparent difference between them. It is more so when you need to calculate previous values with the current value. This is made easy by using an inbuilt function in Tableau called the Lookup function. The Lookup function, as it says in the name, “Looks up” for previous values depending on the number of places you enter. In the example below, the Lookup function is used to see two places below the actual value.

See the example of a Tableau Lookup value in another column below.

For this, the values of “Sales” are sought out for two places above the actual value.

Syntax

The Syntax for using Tableau Lookup with condition is:

LOOKUP(expression, [offset])

Where, 

expression is the formula or column used and,

[offset] is the number of values above the current value.

How to use Lookup function in Tableau?

Learn how to create a Tableau dashboard design by following the steps below.

Step 1: In the Tableau Application, either select a new Book or skip this step if you’re newly opening the Tableau application.

It will open a new workbook, where you can add sheets and dashboards by selecting it at the bottom of the application.

Step 2: Drag and drop the dataset you want to perform various analyses.

The Sample-Superstore dataset is used for this example.

Step 3: Go to the new sheet to start your visualization.

Step 4: Drag and drop the Order Date in the columns section.

Step 5: Right-click on the “Order Date” and select the “Custom” format as shown.

Select the custom format as “Month/Year”.

Step 6: Place the “Sales” feature in the Columns tab, right-click and select the “Discrete” value.

Now these two are in the rows section.

Step 7: Create a new Calculated field.

Step 8: In the calculated field, name the field.

Step 9: Write the formula to find the difference between the “Sales” feature and the Tableau Lookup value in another table (previous value).

In this formula:

• SUM([Sales]): This calculates the sum of the values in the [Sales] field.
• ZN(): In Tableau, ZN() is used to replace NULL values with zeros. It converts a NULL value to zero, and if the value is not NULL, it returns the original value.
• ZN(SUM([Sales])): This wraps the sum of sales within the ZN() function, ensuring that if the sum of sales is NULL or empty, it will be treated as zero.
• LOOKUP(): In Tableau, the LOOKUP() function is used to look up the value of an expression at a specified offset in the data partition. It is often used in table calculations.
• LOOKUP(ZN(SUM([Sales])),-1): It is used to get the previous value for the “Sales” column.

Step 10: Place the new calculated field in the Text section of the “Marks” tab.

With that, a lookup table with the difference between the Tableau Lookup previous value is done.

Examples

See some exciting examples with which you can use the Tableau Lookup value in another table in different ways. Some of these uses Calculated fields and require you to use formulae, and in others, the Lookup value can be defined without any formulae. There are examples covering both scenarios below.

Example #1: Calculate difference between current and previous values

In some cases, you want to find the difference between the current value and the previous value. It is an integral part of data analysis that is tied loosely to data science. In this example, the Sample-Superstore dataset is used, and the difference in Discount is calculated using Tableau Lookup’s previous value function.

Step 1: Open a new Workbook. In that, connect it with the Sample-Superstore dataset.

Step 2: A “Sheet1” is automatically defined in the new workbook. Go to that.

Step 3: In the rows, select “Ship Date” and click on the ‘+’ in the YEAR to expand to QUARTER as shown.

Step 4: Drag and drop “Discount” in the “rows” tab. Select “Discrete” for it to show numerical values instead of a graph visualization.

Step 5: Create a calculated field to find the previous discount value.

Step 6: Use the Tableau Lookup previous value as shown. ‘-1’ is used to show the previous value.

Step 7: Add this calculated field to the Rows and make it “Discrete” by right-clicking on the calculated field and selecting “Discrete.”

Below is the table.

Step 8: Create a new calculated field to find the difference between the current value and the previous value.

Step 9: In the field, subtract the Discount value with the “Prev_Discount” calculated earlier.

Step 10: Drag and drop the calculated field in the “Text” section in the “Marks” tab.

Below is the final table.

Example #2: Calculate Percentage change

Similar to the above example, you can calculate the percentage change in the “Sales” of different sub-categories over the years. This can be done using the Lookup function, which can be used to get the previous value and make percentage change comparisons based on that.

Step 1: Create a new Worksheet if you have already uploaded the Sample-Superstore dataset.

Step 2: Drag and drop the feature “Order Date” in the Columns section and the “Sub-Category” in the Rows section.

Step 3: Create a new calculated field.

Step 4: In the calculated field, first, write the formula to find the difference between the current and previous values of the “Sales” feature.

Here,

• SUM([Sales]): This calculates the sum of the values in the [Sales] field.
• ZN(): In Tableau, ZN() is used to replace NULL values with zeros. It converts a NULL value to zero, and if the value is not NULL, it returns the original value.
• ZN(SUM([Sales])): This wraps the sum of sales within the ZN() function, ensuring that if the sum of sales is NULL or empty, it will be treated as zero.
• LOOKUP(): In Tableau, the LOOKUP() function is used to look up the value of an expression at a specified offset in the data partition. It is often used in table calculations.
• LOOKUP(ZN(SUM([Sales])),-1): It is used to get the previous value to the “Sales” column.

Step 5: To convert it to a percentage value, divide it by the previous value using the Tableau Lookup previous value method.

Here,

ABS(): Calculates the absolute value of a number. It ensures that the denominator (the value obtained from the previous row) is always positive, avoiding division by zero errors.

ABS(LOOKUP(ZN(SUM([Sales])),-1)): Calculates the absolute value of the sum of sales from the previous row.

By adding it to the Text component in the “Marks” tab, you get this (drag and drop in the “Text” tab” as shown in previous examples).

But this doesn’t have the percentage symbol.

Step 6: Right-click on the calculated field in the “Marks” tab and select “Format.”

Step 7: Select “Percentage” and select the number of decimal points you want. Here, the default value of 2 is chosen.

It is the final table.

With that, the percentage difference in Sales compared to their previous value has been identified.

Example #3: Calculate Moving Average

Here, using Tableau’s functionalities, you can calculate the moving average for the number of lookup values that you want. In this case, there is no need for formulae or mathematical functions. It requires almost no coding. Follow these simple steps to learn how to calculate the Moving Average.

Step 1: Create a new Worksheet under the Sample-Superstore dataset.

Step 2: Drag and Drop the “Order Date” and select the “Month” as shown, which converts it into a continuous value.

Step 3: Select “Profit” and place it in the “Rows” section.

Drag and drop Profit to the “Text” component in the “Marks” tab.

Step 4: Right-click on “Profit” and select “Add Table” calculations.

It opens a new popup where you can customize the values to your preference.

Step 5: Select the “Moving Calculation” and change it to “Average”.

Step 6: Select “Null if there are not enough values” and set the “Previous Values” to 3. Since the first three values cannot be used in the Lookup, they are declared as “Null.”

It is the final graph.

Important Things To Note

• Understand how Tableau partitions the data when using the Lookup function.
• Specify the offset Tableau parameter carefully to ensure you’re looking up the correct value. 
• Positive offsets refer to rows below the current row, while negative offsets refer to rows above the current row.
• Use the ZN() function to handle NULL values appropriately, ensuring consistent behavior in calculations.
• Don’t assume default behavior when using the Lookup function. 
• Don’t forget to handle edge cases, such as situations where the offset goes beyond the boundaries of the data partition.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to What Is TABLEAU LOOKUP. Here we explain how to use Lookup value in different table with examples and points to remember. You can learn more from the following articles –

• Tableau Parameters
• Tableau Case Statement
• Tableau Datediff