Top 25s etc. in Table cards

Mairs

We've put together a table card of Top 25 corporate travellers for one of our customers based on monthly spend. There are way more than 25 travellers however, and they want us to create a summary row for the remaining x travellers. The number of remaining travellers can vary month on month though so I need a way to hoover all these up.

Is there a way to do this? I've looked at Rank and Window in the ETL, but I can't get my head around how this would work. Any ideas would be really helpful.

Thanks

ArborRose

You can create a Magic ETL using ranking and filtering tiles to separate the top 25, then aggregate the remaining records.

Rank Travellers by Their Monthly Spend - Use the Rank and Window tile in the ETL. Use partition by for any grouping you need. Order by - choose your spend column and set the order to descending so the highest spenders are at the top. You can choose row number to give each traveler a unique rank based on the amount spent. This will create a new column.

Separate the Top 25 From The Rest - Use filtering tiles to create two streams - top 25 travelers where Rank is less than or equal to 25. And another filter to ran where greater than 25.

Aggregate The Remaining Travelers - Use Group By tile to aggregate (count/sum) the records. Such as remaining spend, remaining traveler count.

Append Top 25 and Summary Row - You can union using the append tile to put the top 25 and the aggregated row together.

Note also that cards have a setting to show top X records. If you decide to make some type of App or dashboard.

ggenovese

Another option might be to create a beast mode to rank each traveler and then use subtotals to collapse the rows for the travelers whose rank is greater than 25. Assuming you have a field named `Traveler` and a field named `Spend`, first create a beast mode called `Top 25 Ranking`

— Rank Each Traveler up to 25 and then place remaining in 26
CASE 
WHEN SUM(COUNT(DISTINCT `Traveler`)) OVER (ORDER BY sum(`Spend`) DESC) <= 25 

  THEN SUM(COUNT(DISTINCT `Traveler`)) OVER (ORDER BY sum(`Spend`) DESC)

  ELSE 26

END

Place the Top 25 Ranking on the card, along with Traveler and Sum of Spend

Next, in the properties menu to go Subtotal Rows and select "Show subtotal rows" and "Suppress single item subtotals". By adding Subtotals you can now expand and collapse the Top 25 Ranking to hide the Travels who didn't make the Top 25.

Finally, when I tried this on some sample data I noticed that the subtotal for ranking #26 was summing up to be the same as the total so I created another beast mode called `Spend By Traveler` so that the spend numbers would subtotal correctly:

SUM(SUM(`Spend`)) OVER (PARTITION BY `Traveler`)

If this works for you then you should end up with a table that displays 26 rows, with the last row that can show/hide the travelers who didn't make the top 25. I was testing with a smaller dataset so I made a Top 5 version

Collapsed/Expanded Views: