Pareto Report - trying to limit number of categories returned

C_Schaffer

So... I'm attempting to abuse the Pareto report type a bit (fine, probably a lot).

 

I presently have two reports, one that includes a full category set, like the report intends, and one that is just the top 10 categories.  The problem is that I'm grabbing the top 10 at present by manually filtering them, which is obviously not a great solution as we except quite a bit of change over time.

 

Is there any way of limiting the number of items displayed as can be done for other report types?  or an easy way of producing a rank order via Beast Mode for this purpose?

 

These are the thumbnails of what I currently have in place:

 

I'm mostly trying to do this because one of our data consumers really likes that percent of total line.

 

For reference I am also aware of the following knowledge base article: https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

 

That article, based on what I've seen in previously asked questions, seems like it may be the actual road forward.  But hey, can't hurt to see what other suggestions may be available

BlueRooster

The only other option that I'm aware of would be to build this in a SQL transform. You can't do this via BeastMode as having aggregation functions (which would be needed to rank your items) are not supported in the Filters section.

 

Via SQL it would look something like this :

SELECT `Identifier`, SUM(`Value1`), COUNT(`Value2`)
FROM source_dataset
GROUP BY `Identifier`
ORDER BY SUM(`Value1`) DESC
LIMIT 10

Obviously your aggregations would be different depending on what you're showing, but that's the general idea (group, sort, then limit)

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

BlueRooster

The only other option that I'm aware of would be to build this in a SQL transform. You can't do this via BeastMode as having aggregation functions (which would be needed to rank your items) are not supported in the Filters section.

 

Via SQL it would look something like this :

SELECT `Identifier`, SUM(`Value1`), COUNT(`Value2`)
FROM source_dataset
GROUP BY `Identifier`
ORDER BY SUM(`Value1`) DESC
LIMIT 10

Obviously your aggregations would be different depending on what you're showing, but that's the general idea (group, sort, then limit)

 

Sincerely,

ValiantSpur

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

DataMaven

But @Valiant...I need it to be dynamic, depending on filters...any thoughts?  Any new developments?  

 

PS - Why did you change your name?  

BlueRooster

Still not a great option available that I'm aware of. Can't use RANK or ROW_NUM functions at the card level and you still can't set a max # of columns returned for the Pareto card I feel like there might be an option using interaction filters but I think it would be cumbersome in practice.

 

And the name change was just the natural evolution. It's like when Mark Zuckerburg went from 'The Facebook' to just 'Facebook'. It's cleaner 

 

Valiant