Display top 4 and Rest all should be aggregated as OTHER

Need to show only top 4 and rest all should be aggregated as OTHER

Say for example-

I have

Attribute Metric
A 50
B 60
C 10
E 80
F 40
G 30

it should be shown as below

Top four and rest all should be aggregated as OTHER

Attribute Metric
E 80
B 60
A 50
F 40
OTHER 40

This is my requirement please let me know your suggestions

Thanks
Basavaraj

Basavaraj -

You can use our SQL visual type to run a query similar to this:

SELECT 
    CASE WHEN `rn` <= 3 THEN `cust_name` ELSE 'Others' END as `cust`,
    sum(`total_sales`)
FROM(
    SELECT TA_0.`customer_name` as `cust_name`, 
    sum((TA_0.`sales`)) as `total_sales`,
    rank() over (order by sum((TA_0.`sales`)) desc) as `rn`
    FROM  `default`.`superstore_sales` as TA_0
    GROUP BY TA_0.`customer_name`
    ) as T1
GROUP BY 1

This would output something like:

image

You can then use the Dataset directly from SQL feature to create a dataset using this output to then visualize from.

Hope that helps.

3 Likes

Shaun

This looks good. Thank you.

I will check and get back to you.

Hi Shaun,
I tried to use your query and it works! I used it to calculate the percentage of the total:

image

1 Like

Hi Shaun, may i know if there is anyway to do this use case without SQL visual? This is because the top n categories changes with the dashboard filter.

We’re using the top/bottom K setting to limit the display but the percentages of the bars do not add up to 100% hence would like a ‘Others’ bar.

image

Kindly advise, thank you.

@esther using Derived Data would be your other option. You would need to create a Rank function for #INC across your Categories, and then save that expression as Derived Data. Below is an example where I’m ranking regions by sum(sales):

RANK() over ( order by sum([sales]) desc) as 'sales_rank'

You can then use that Rank in a new expression like this to change the Category names that are displayed (i.e. only show 1-4 and everything else as "Other’).

Now you should see a more condensed bar chart afterwards, and you can even color your new dimension categories if you duplicate the expression to your Colors shelf:

Before and After:

Hi Tadd,

Thanks for the reply. I tried the following query but it didn’t group well:
derived data: rank() over (order by count(distinct(inc)) desc) as ‘inc_rank’
category: case when [inc_rank] > 10 then ‘Others’ else [Category] end

The ‘Others’ category is actually all the category as total INC = 119k.
Also, I noticed that derived data takes in the existing filters in the filter bar, will it be dynamic and change when values in the filter bar are changed?

Please advise. Thank you. :slight_smile:

Hi Tadd,

Sorry, could you kindly help me in the questions posted earlier?
I’m unable to group to ‘OTHERS’ with count(distinct()), it seems to only work with sum().

Thanks!