Rank within subsets of data in spotfire

Question

I am trying to rank subsets of my data in spotfire.

Lets say I have a data table that has the following 6 columns:

Individual, City, Zip Code, State, Amount1,and Amount2.

There are thousands of Unique Individuals in each Zip Code and many Zip Codes within each State. How would I display only the data from the top 5 Zip Codes within each State (as defined by the SUM()of Amount1)?

To summarize the order of operations; I want to sum up Amount1 for each Zip Code, then Rank the Zip Codes in desc order within each State (just an intermediate step for explanatory purposes) and finally, only display the top 5 Zip Codes within each State.

All I could think of was to create a calculated column that would return the Zip Code if it satisfied my conditions and NULL if it did not. I don't think its the best but here is the code I started with:

case  WHEN DenseRank(Sum(Sum([Amount1]) over [ZipCode]) over [State],"desc")<6 then [ZipCode] ELSE NULL END

Any help would be great. Thanks!

Answer 1

Thanks for the clarification in the comments.

DenseRank(Sum([A1]) OVER ([ZipCode]),"desc",[State]) as [Rank]

The above function will give rank your [ZipCode] within its respective [State] based on the SUM() of an amount in column [A1]. DenseRank() will NOT skip a ranking number if there is a tie. The means you could have more than 5 [ZipCode] in your top 5. Use Rank() if you want to avoid this.

Then, you can create a calculated column for your filter panel, or just filter it in the "Limit Data using Custom Expressions" section of your chart.

If([Rank] < 6,"Top 5", "Other") as [Zip Rank in State]