2 Replies Latest reply on Sep 7, 2018 10:04 AM by Casey Terp

# Minimum Rank for a Category

Hi,

Using the Superstore data, I want to be able to find the top 10 manufacturers by state (by sales) and their rank, but also be able to see their rank in other states. Right now I am able to create a filter that will show the top 10 for each state, but it will not show the rank in other states. The filter I am using is just filtering out those rank values. Ideally what I wad thinking was using a fixed field to find the rank of each manufacturer per state, then take the minimum rank per manufacturer and use that as a filter, but I can't use rank in that way. Any ideas?

Just to clarify, what this would do is first: only display a manufacturer if it falls in the top 10 for any state. second: display the ranks for that manufacturer in every state.

So say we are looking at Apple. If it's the 5th highest by sales for California, it will be displayed. Under California it will show rank:5. If it is the 200th for Florida it will show 200th.

If we are looking at 3D systems, and it doesn't fall in the top 10 for ANY state, it won't be displayed.

• ###### 1. Re: Minimum Rank for a Category

hi Casey,

Tricky one!

So I think you can do it like this...

First I created a RANK formula

[Manufacturer RANK per State]

RANK(SUM([Sales]))

and set it up like this

I then used this formula in another Table Calculation to get the MIN of that RANK (as I've nested a Table Calc in a Table Calc, we get the option to set the compute differently for each)

[Manufacturer RANK per State Min]

WINDOW_MIN([Manufacturer RANK per State])

This part is set up like this

I then brought this onto the filter shelf, and set it to values 1-10

Finally, and this is more to check it's working...

I created a Table Calc filter for the Manufacturer. As Table Calculation filters are applied last, it means that the RANK function still have access to the data and so can still compute the correct RANK. I like to think of these as filtering the view, but not the underlying data.

Hope that makes a bit of sense...quite a few advanced concepts here.

1 of 1 people found this helpful
• ###### 2. Re: Minimum Rank for a Category

Thanks Simon that worked perfectly. I didn't even think of using window_min, I was just trying the regular min function and getting an aggregation error.

Casey