Using Percentiles to Bucketize in Beast Mode Query
I have a table with a text dimension (Name) and an associated measure (Score).
I wish to do the following:
- Calculate if the Score for each Name is in the top 20th percentile of all entities (highest)
- If the Name is inside the 20th percentile, do not update the Name.
- If the name is outside of the 20th percentile, change the Name to 'Other'
You can do this by using Magic ETL. Use the Rank & Window tile and Rank your scores. Add a Group By tile to get a Count of the total rows in your dataset. Join it back to the Rank & Window tile so that the total row count is now a column next to every rank. You can then do the math in the ETL or in a Beast mode to determine the percentile. You can also change the name in the ETL if you want as well so you don't have to do any beast modes.
Hope this makes sense.**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
You can do it with a beast mode which will be responsive to any filtering you apply to your card (the percentiles will be recalculated with respect to your filters)
CASE WHEN SUM(SUM(1)) OVER (ORDER BY `Score`) / SUM(SUM(1)) OVER () > .2 THEN ‘Other’ ELSE `Name` END**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
while @GrantSmith 's solution will create the buckets, you will still have one row on the axis for each value. it will not aggregate to the bucket level. (in other words you'll see 'other' and 'name' multiple times on the axis instead of having one row "other" and one row "name"
if that's your desired outcome, you must pre-aggregate the data as Mark has suggested or use DataSet Views to create a similar outcome. Because the data is pre-aggregated and 'bucketized' in ETL or a View, you'll be able to then aggregate on that column.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 195 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 81 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive