Filtering: Dynamic rankings in a filtered view?

nash · January 21, 2015 3:15AM

I need to show the Top 3 brands depending on what is selected in the filters. For example there are 10 brands in the data, I select only seven and so I need the top 3 brands of seven selected.
At the same time I need to calculate what is the share of the every of these three brands of all the brands which are selected.
And the last requirement is to show the difference for every brand to the year before.

So far I work it out for all brand which are in the data with formulas in the fields.
However I would need more dynamic.

Is there a way to get it are you working on it?

tjbate · January 21, 2015 5:21AM

Natascha - The RANK function will calculate global rankings that do not change with filters. However, you can use a hidden Rank field to sort on, so that if the filter shows only 3 brands, they will be sorted according to their global rank.

Please look at the associated posts here with related tags Pareto, top/bottom N, and ranking (click on tags at right)

paola · January 21, 2015 7:14AM

Regarding the calculations, if you add 2 formula fields for % contribution and YoY difference, the values in the table will not respond to filtering, which is what you require?


SUBSET_SUM([Value], SUBSET([Brand]))
/
SUBSET_SUM([Value], SUBSET)

# this will work out contribution of each brand towards the Total, irrespective of the Year. Please use Subset2, as below, if you wish to see this contribution value split in each year.

YoY difference:


SUBSET_SUM([Value], SUBSET2([Brand],[Year]))
-
SUBSET_SUM([Value], SUBSET2([Brand],[Year],[Brand],[Year]-1))

Note: you can easily create the [Year] field by duplicating the date field, but please make sure that you re-format it later to integer.

This is the post where you can find both formulas and demo files showing dynamic ranking:
http://forums.visokio.com/discussion/comment/9465/#Comment_9465

Welcome!

Categories

Ideas Parade

Tagged