Top 25s etc. in Table cards

Mairs
Mairs Member
in Charting

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

Answers

  • ArborRose
    ArborRose Coach

    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.


    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ggenovese
    ggenovese Contributor

    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:

    image.png
    image.png

Categories