How can I index top 5 on latest complete date

Question

Attached is mocked up data as I am unable to post real data due to security. I need to return the top 5 results per manager based on the latest complete period only (as per the excel screenshot) showing variance from previous period. Does anyone have any ideas on how I can do this? My data source is actually a lot more complicated than this so i have limited options to change the backend. Hoping there is something I can do with table calc/index etc. Thanks

Tableau Community Forums Member (Inactive) · Accepted Answer

Answer found here  https://community.tableau.com/s/question/0D54T00000C6m5VSAR/dynamic-sorting-by-most-recent-available-date

James Emery · Answer

There are several ways to get a top 5 list. I recommend reading  Four Ways to Perform a Top N in Tableau - The Flerlage Twins: Analytics, Data Visualization, and Tableau .  The method described here uses RANK.   First, a calculation is needed to return the count of the most recent period C_Count if Period is Max { FIXED [Mgr],[Staff]: SUM(IF [Period]={ MAX([Period]) } THEN [Count] END)} Because an identical staff name might appear with multiple managers, the LoD should be fixed on both [Mgr] and [Staff]. Each row's [Period] value is compared to the maximum [Period] value for the data. If they are the same, it returns the [Count]. For each [Mgr] and [Staff] pair, the [Count] is summed.  In your example, the Max period is not 10/3/2022 shown in the screenshot. Instead, it is 10/10/2022. To match the screenshot table, I used a Period context filter to exclude 10/10/2022 (Because a Fixed LoD is used, the filter must be added to context. Because you want a variance from the previous period, you cannot filter to just the desired max.)  Next, create a calculation using one of the RANK functions for the desired aggregation //C_Rank of Count RANK(SUM([C_Count if Period is Max])) Instead of a separate calculation, RANK could be added to the previous calculation.  Finally, create a filter calculation for the values you want to keep //Filter_Rank <=5 [C_Rank of Count]<=5 Place it on the filter shelf and set to TRUE

How can I index top 5 on latest complete date

Tableau Desktop & Web Authoring

Related Learning

More Help