RANK() in pivot table issues
Hi,
I have a pivot table and I am trying to only show a top 10 based on a field and then bucket the rest into an "Others" group.
My data contains monthly sales per country.
My table is setup as follows.
Rows: Country beastmode below
Columns: Date
Values: sum(sales)
My beast mode for Country is
case when RANK() OVER(ORDER BY SUM(`Sales`) DESC) <= 10 then `Country` else 'Others' end.
The top 10 show up fine but the others row has **** in the cell and the warning.
My sales column is doing a SUM so I am not sure what else to try. I can't say I have ever attempted something like this.
Am I trying something that isn't possible?
Thanks
Answers
-
It may be that some of the values are null. Have you tried using coalesce? Something like…
CASE
WHEN RANK() OVER (ORDER BY COALESCE(SUM(`Sales`), 0) DESC) <= 10
THEN `Country`
ELSE 'Others'
END
Also, verify your data doesn't have any type mismatch.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@ArborRose thanks but it didn't seem to help.
0 -
The error seems to indicate you need to address the pivot where you have more than one match to a location. You don't show any sample data so its difficult to guess where it sees an issue.
Check your CASE statements to make sure you are categorizing the data properly with no logic errors in multiple categories. Make sure your SUM aggregation are valid for all data. Perhaps test with sample data to see if you still get the warning. This can help isolate issues with specific data values.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
I have tried this with some simple data but still doesn't work.
When converted to a normal table the problem can be seen
0 -
@MichelleH no sorting at all.
I have never tried to "bucket" data using a rank in this way before so perhaps it isn't possible.
0 -
@Jones01 The rank is likely what's causing the issue here. Since beast mode is dynamic, it is having trouble ranking a dimension while simultaneously also defining that same dimension. Here are a couple options you could consider instead:
- Change your chart type to a bar chart, where you can use the native "Max Number of Bars" option in chart properties
- Calculate the rank in a dataflow using the Rank and Window tile, which you can then reference in your buckets. Note that this ranking will not respond to card filters
1 -
@MichelleH thanks, I thought it was an odd one.
Unfortunately our users like to be able to flex the filters and the date range so I don't often revert to using ETL's for this sort of stuff. I need a pivot table as they want other data columns on display at the same time.
I think they'll have to live without the "others".
This where I miss just being able to write a query …perhaps a feature request for the future haha..
0 -
Curious….when I create an ETL and build a rank and formula for top sales, it aggregates Other just fine.
When I try to do it on the card itself, it can't seem to aggregate Other. It tries to display all Other individually. Although I tried to eliminate all other columns from the dataset.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
as @jaeW_at_Onyx rightly pointed out to me domo can't do 2 group bys within cards.
This would require a query to do the rank and then another group by on the result of that, along the lines of this.
select case when rank <= 3 then country else 'Others' end, sum(sales) from (
select RANK() OVER (ORDER BY SUM(sales) desc) as rank, country, sum(sales) from countrysales group by country)
group by case when rank <= 3 then country else 'Others' end0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive