Using the LOOKUP() function in Tableau

Today, we will talk about the LOOKUP() function in Tableau.  The LOOKUP() function allows you to find values that exist elsewhere in your table, or not in the table at all.  At its most basic, it returns a value from a different row in the partition.  It works exactly like INDEX(), TOTAL(), and all of the WINDOW functions.  As usual, we will use the Superstore Sales sample data set in Tableau.

Step 1:

• Create a table!!!

Sales by Year

Now, let's create a simple lookup that returns the sales from the previous year.

Step 2:

• Lookup SUM( [Sales] ) from the previous year
• Create the following calculated field

Previous Year Sales

Now, let's see how it works.

Sales by Year (with Previous Year Sales)

Remember how we defined our LOOKUP() location as -1?  That means it looks one space backwards in the partition.  So, what would happen if we changed the order of the year?

Step 3:

• Change the sort order of the years to be descending.

Sales by Year (with Next Year Sales)

The calculation changed from previous year to next year, without us having the change the formula.  This is the extremely important aspect of the LOOKUP() function.  We can also use this LOOKUP() value in calculations.

Step 4:

• Change the sort order back to ascending
• Calculate the YoY Growth
• Create the following calculated field

YoY Sales Growth

Now, let's see if it works.

Sales by Year (with YoY Growth)

Now that we have a basic understand of how LOOKUP() works, let's talk about 2 great uses for it.

Use 1: Coercing a calculation to be a table calculation.

You might be asking "Why would we want a calculation to become a table calculation?"  If you remember an earlier post, Creating Table Calculations on Values outside of the Filter Range, you know that table calculations are calculated after other calculations.  This means that if we can force a calculation to be a table calculation, we can control WHEN it is calculated.  This is extremely important in more complex scenarios.  Check out that post for a specific case where it is helpful.  The important aspect to recognize about this use is that SUM( [Sales] ) and LOOKUP( SUM( [Sales] ), 0 ) return exactly the same value, yet are calculated at different times.

Use 2: Looking up values across time.

This is a huge category that very few people will ever fully utilize.  To emphasize it, I will show you a very extreme example, you can look up virtually any types of values you want simply by sorting your data in different ways.  First, we created the following calculated field:

Sales from Same Place in Previous Partition

Here's what it did:

Sales by Quarter (with Sales from Same Place in Previous Partition)

First, notice that we mixed up the Quarters in each year so that they do not match.  Then, we applied our LOOKUP().  You will notice that the 2nd column contains the value from the same place in the previous partition, regardless of what the date was.  This is how LOOKUP() actually works.  We actually exploited this concept in a project with a customer who had a fiscal calendar with days that are mixed up similarly to the above graph.

LOOKUP() is one of the most powerful functions in Tableau.  There are so many ways to exploit these features to calculate some truly incredibly values.  I hope you found this informative.  Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn