topic Display top 5 and the rest in Others in QlikView

Display top 5 and the rest in Others

Tue, 04 Oct 2011 07:06:41 GMT

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

Display top 5 and the rest in Others

Michiel_QV_Fan — Tue, 04 Oct 2011 07:29:39 GMT

You can achieve this with the Pie chart.

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

Display top 5 and the rest in Others

kji — Tue, 04 Oct 2011 07:45:03 GMT

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

Re: Display top 5 and the rest in Others

Miguel_Angel_Baeyens — Tue, 04 Oct 2011 07:52:56 GMT

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

Display top 5 and the rest in Others

Tue, 04 Oct 2011 12:19:42 GMT

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

Regards

Joe