Display top 5 and the rest in Others

[#1]

Good Morning All,

In the Bar Chart, I would like to display the top 5 of the carrier codes and their count of invoices and the rest of the carrier codes should group it in the Others column.

Is it possible...

For example I have attached the QV file.

Regards

Joe

[Michiel_QV_Fan]

You can achieve this with the Pie chart.

I haven't found a solution in the bar chart.

[kji]

On Presentation tab, set Max Visible Number to 5, and check Show Others.

[Miguel_Angel_Baeyens · Accepted solution]

Hello Joe,

You can use the following calculated dimension

=If(Match([Carrier Code],
$(=Chr(39) & Concat(If(Aggr(Rank(Sum([Number of Invoices]), 4, 1), [Carrier Code]) < 6 
, [Carrier Code]), Chr(39) & Chr(44) & Chr(39)) & Chr(39))) > 0, [Carrier Code], 'Others')

Or the more simple

=If(Aggr(Rank(Sum([Number of Invoices]), 4, 1), [Carrier Code]) < 6, [Carrier Code], 'Others')

The difference between this and the above is that when you select in the chart using the first dimension, you will be selecting only values in the field "Carrier Code". In the second, although it seems the same and it will work as well, you will be selecting the actual aggregation instead of the value of "Carrier Code" and that may return unexpected results in the other charts, that's why I prefer to use the former.

And the following expression instead of the existing

Sum([Number of Invoices])

That will return the top 5 and their invoices and the rest of Carriers under "Others".

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

Thanks a lot Miguel Angel exactly matches with my requirement.......

Regards

Joe