Rank within subsets of data in spotfire
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]
NAlverson
Updated on June 07, 2022Comments
-
NAlverson about 2 years
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 eachZip Code
and manyZip Codes
within eachState
. How would I display only the data from the top 5Zip Codes
within eachState
(as defined by theSUM()
ofAmount1
)?To summarize the order of operations; I want to sum up
Amount1
for eachZip Code
, then Rank theZip Codes
in desc order within eachState
(just an intermediate step for explanatory purposes) and finally, only display the top 5Zip Codes
within eachState
.All I could think of was to create a calculated column that would return the
Zip Code
if it satisfied my conditions andNULL
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!