Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
BI Consultant
You can achieve this with the Pie chart.
I haven't found a solution in the bar chart.
On Presentation tab, set Max Visible Number to 5, and check Show Others.
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.
BI Consultant
Thanks a lot Miguel Angel exactly matches with my requirement.......
Regards
Joe