RANK() in pivot table issues

Jones01

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

ArborRose

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.

Jones01

@ArborRose thanks but it didn't seem to help.

ArborRose

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.

Jones01

I have tried this with some simple data but still doesn't work.

When converted to a normal table the problem can be seen

MichelleH

@Jones01 Is there any sorting in your card? If there are, try removing the sort fields to see if that removes the duplication.

Jones01

@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.

MichelleH

@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

Jones01

@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..

ArborRose

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.

Jones01

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' end