Column Totals

Rick

Hello,

I am building a "top 20" card, showing the top 20 past due customers. Separately I have a total past due that looks at the entire portfolio. What I would like to do is to display the total of the top 20 comparing it to the total past due….

Example, the total past due 1-30 days late is $5,100.000. The total of the top 20 customers is $4,500,000.

At the top of the page I show the total 1-30 and total outstandings, which is a sum of all of the rows by column. What I want to also show is the top 20 1-30 total. Below these summary fields I have the table showing the 20 rows of the top 20. How do I get the summarized top 20 amount showing? I cant get it filtered to just the total of the top 20.

MichelleH

@Rick I'd recommend adding a customer rank column to your dataset using the Rank & Window tile in MagicETL. Then you can filter your summary cards to Rank <= 20 to show the total of the top 20.

marcel_luthi

The above solution works great, as long as your card/dashboard is not meant to be responsive to user filters and you have clearly defined partitions over which the rank function should be applied. If your top 20 is meant to be responsive at the view level, I usually show a Horizontal Bar char and limit the number of bars to show sorting on totals, this way not only you give visibility into the top total, but also who they are. (but this uses a lot more real-state than a single number card).

MichelleH

@Rick I'd recommend adding a customer rank column to your dataset using the Rank & Window tile in MagicETL. Then you can filter your summary cards to Rank <= 20 to show the total of the top 20.

pauljames

@MichelleH @Rick , yes the rank and window would allow for a top 20 ranking then you could filter on your card with a case statement beastmode.

CASE when 'rank and window column name' < = 20 THEN 'Top 20' ELSE 'Not Top 20' END.

So you'd filter by selecting 'Top 20' customer category to get the data you need showing.

marcel_luthi

The above solution works great, as long as your card/dashboard is not meant to be responsive to user filters and you have clearly defined partitions over which the rank function should be applied. If your top 20 is meant to be responsive at the view level, I usually show a Horizontal Bar char and limit the number of bars to show sorting on totals, this way not only you give visibility into the top total, but also who they are. (but this uses a lot more real-state than a single number card).

trafalger

Window functions in a beast mode are a good way to make this dynamic!

https://community-forums.domo.com/main/discussion/56212/dynamic-rank-beast-mode