Keep Only Recent Transactions in Tableau

A couple weeks ago I was teaching a course and received a question from a student. Her question was “How can I use Tableau to only show the most recent 3 transactions per customer?” I thought I’d have an answer for her quickly, but I was wrong.

My first thought was, let’s just use the “RANK” function to accomplish this. We’ll use it as a Table Calculation to determine the most recent transactions by customer. I was feeling confident until I saw this:

23-1.png

Turns out you can only apply the rank function to numeric fields. Dates do not count.

This had me stumped for a while. I did some research and couldn’t find anything. Then it hit me. “What if we can turn the date field into a number?”

Instead of ranking a transaction by a date, I ranked by the difference between a start date and end date. I picked a start date I knew would be before any of the order dates in my data-set to be safe.

23-2.png

*Note that I added the “AVG” aggregation for two reasons. First, the “RANK” function only computes against aggregated measures. Second, there might be multiple lines of data for each transaction and I don’t want the difference in days duplicated.

Here’s how you can apply this field and use it as a filter. Let’s say we have a bar chart like this:

23-3.png

When we drop “Transaction Recency Rank” on label it computes an overall rank (not a unique rank per customer).

23-4.png

Since “RANK” is a Table Calculation function, we can change the scope of our calculation (Pane (Down) provides a unique rank per customer).

23-5.png

Notice the difference:

23-6.png

Last, we can drag a copy of the “Transaction Recency Rank” field to the Filters Card and only keep 1 - 3.

23-7.png

We are left with only the 3 most recent transactions per customer:

23-8.png

 

Have additional questions or need help with a problem? Email eric.parker@onenumber.biz.