Consolidating dimension values under the tag "All Others"

[former_member184543]

Hi All,

Recently I had come across a requirement of consolidating a list of values in a dimension by showing only Top 5 and remaining as "All Others". I am sure many of us would come across such a requirement and the following methodology helped me with the same.

I will demonstrate the same with e-Fashion as data source.

For my demonstration, I brought in City, Sales($) as a part of the data provider.

Firstly, create a measure which should do a ranking on the Sales ($) Rank variable.

Secondly, create a dimension (be careful, this has to be a dimension) which should use the rank object created to build the logic as shown below

And the output would like this

This shows that the values from Rank 6 onwards are conslidated under "All Others". Idea is we have to use some sort of measure in dimension to apply this logic. It can be a count, rank or whatsoever you can think about. The "if loop" in the dimension variable acts like a "for loop" in java or elsewhere, wherein it gets rank 1, and puts in the corresponding [City], then rank 2 and puts in the corresponding [City]. This will continue until [Rank]<=5. But once it's over this rank, the condition fail and goes to "All Others" until the end of the list and the measure which is Sales ($) aggregates (this is also important, your measure needs to aggregate) accordingly.

There are more tricks around "All Others" of which one of them I will cover here and the remaining in another blog of mine which will be a Part 2 to this blog. There are few things that we need to take care in Cross Tab as against Vertical table and for All Others which will be explained there.

Just in case you wanted to Sort the Sales Revenue in Descending order and always want All Others as the last one in the table, this is what you should do.

First create a dimension variable with the following logic as shown below:

Insert it into the block and then sort the [var_Sort_AllOthers] column in ascending order

Then sort the Sales Revenue column in Descending order.

You can then hide the unnecessary columns like making width to 4px and other cosmetic works based on your needs.

Hope this helps in some way.

Thank you.

Best,

Sanjo