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:
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.
*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:
When we drop “Transaction Recency Rank” on label it computes an overall rank (not a unique rank per customer).
Since “RANK” is a Table Calculation function, we can change the scope of our calculation (Pane (Down) provides a unique rank per customer).
Notice the difference:
Last, we can drag a copy of the “Transaction Recency Rank” field to the Filters Card and only keep 1 - 3.
We are left with only the 3 most recent transactions per customer:
Have additional questions or need help with a problem? Email eric.parker@onenumber.biz.