Beast Mode for Top 20 then Other

lkurtz

I am creating a table to rank sales by each company for the last 3 years and totalling the revenue. 

I would like to show in a table the Top 20 companies by Total 3yr Revenue with the remaining companies aggregated in a row named "Other" rather than the individual company names.

I was trying a beast mode to rank each company by their individual total revenue divided by the total revenue for the entire dataset for a percentage, but I can't seem to get it to work.

Please help! I know how to do this with properties in a pie chart, but not in a table. 

Thank you in advance!

Example table:

Company	2015 Rev	2016 Rev	2017 Rev	Total Rev
Co A	$1,000.00	$1,000.00	$1,000.00	$3,000.00
Co B	$900.00	$900.00	$900.00	$2,700.00
Co C	$800.00	$800.00	$800.00	$2,400.00
Co D	$700.00	$700.00	$700.00	$2,100.00
Co E	$600.00	$600.00	$600.00	$1,800.00
Other	$5,000.00	$5,000.00	$5,000.00	$15,000.00
TOTAL	$9,000.00	$9,000.00	$9,000.00	$27,000.00

jhl

As stated here there is afaik no way to create or check a rank that is not already in the data by using Beast Mode - MySQL (which Beast Mode is based on) does not have these sort of partition functions, which is one of the most limiting factors. A new stable version should be released soon-ish and then eventually Domo will update. Whenever that will be exactly is anyone's guess.

 

There are workarounds - add a rank to your dataset via a dataflow like this in ETL or like this in a MySQL dataflow based on your 2017 revenue and then just beastmode something like

 

case when `rank` <= 20 then `rank` else 'Other' end

 

and format the table to your liking, using the SUM aggregation for the fields.

 

HTH

 

user069835

Trying to do the same thing. Is there an updated solution to this that doesn't require the ETL to be updated? 

jaeW_at_Onyx

No.  You can implement in a DSV (dataset view) but there is no native way in analyzer.

 

https://www.youtube.com/watch?v=eifSYZIcPzg&t=465s