Pareto Report - trying to limit number of categories returned
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
Best Answer
-
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 10Obviously 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.2
Answers
-
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 10Obviously 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.2 -
But @Valiant...I need it to be dynamic, depending on filters...any thoughts? Any new developments?
PS - Why did you change your name?
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
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
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 680 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive