How to find and display only the latest record per group of records?

user_13924935 0
Viewed 96

I'm working with Tableau and I have three columns:

  • Employee
  • Role
  • Date
  • Latest role

For each employee, I need a formula that retrieves each employee's latest role (sorting from date) and displays it in the "current role" column.

Your support is highly appreciated.

tableau-desktop
1 Answers

There are a few ways to tackle this, but the following is a pretty general approach. Divide the problem into two steps.

  1. First define a (LOD) calculation that finds the date of the latest role for each employee. Call it, say, Date of Latest Role { FIXED [Employee] : MAX([Date]) }
  2. Then define a conditional (record-level) calculation that returns the Role for records that have the latest date for their employee (and evaluates to null otherwise by default). Call it, say, Latest Role, IF [Date] = [Date of Latest Role] then [Role] end

Note that it if your data has two roles for an employee that start on the same latest date, then two of your records will have a non-null value for [Latest Role]. But otherwise, this works with few wrinkles.

For example, you can put Employee on the Rows shelf and [Latest Role] on the Text shelf. You can filter out null Latest Roles if you wish — or display Latest Role as an Attribute instead of a Dimension to ignore nulls and show as a ‘*’ if there are multiple latest roles.

Alternatively, you could compute the latest roles in advance with Tableau Prep using a ranking LOD calculation (might be better for very large datasets) or use a table calc Rank calculation (more flexible and dynamic, say if you want to let the user show an arbitrary number of latest roles) Or you could use a windowing query in custom SQL. More than one way to skin a cat, but this is relatively easy and performs well on all but the very largest datasets.

user_4419790
Related