Tableau - Name Rank, by State / Year / Gender

Question

I have a data set downloaded from the Social Security website. The data is in the form below, and contains the popularity (i.e., as defined by Count) of names, by gender, year and state:

State   Gender  Year    Name    Count   First Letter
  AK         F  1910    Anna      10    A
  AK         F  1910    Annie     12    A
  AK         F  1911    Annie      6    A
  AK         F  1912    Alice      5    A
  AK         M  1912    Wilbur     7    W
  AK         M  1912    Thomas     7    T

Within Tableau, I'd like to Top X names by each of these categories (or all if not filters are applied). However, when I use a Top filter in a visualization, the underlying data produced by the filter is the form:

For example, I need the first ranked name, and be able to use filters to see how that changes by year, gender, and state. I'm thinking this might be accomplished by an LOD expression, but not sure where to start.

smb smb · Accepted Answer · 2018-07-30T00:08:23

You can achieve this without needing a LOD calculation. Simply change your filters from standard blue dimension filters to be context filters. To do this simply right click on the filter when it is in the filter pane and click "Add to Context". The filter will be shown as grey. Now all ranks will be calculated after filters have been apllied

Why does this work? It is to do with the Order of Operations in Tableau. A calculation such as rank is a table calculation. As the name implies the calculation is processed on the entire data table before dimension filters are applied. However, when something is a context filter it creates temporary tables which are then used to calculate table cals.

Find out more here: https://www.google.com.au/search?q=order+of+operations+tableau&oq=order+of+operations+tableau&aqs=chrome..69i57j0j69i65j0j69i65j0.3475j0j7&sourceid=chrome&ie=UTF-8

Tableau - Name Rank, by State / Year / Gender

1 Answers