Rank within subsets of data in spotfire
<p>I am trying to rank subsets of my data in spotfire.</p>

<p>Lets say I have a data table that has the following 6 columns: </p>

<p><code>Individual, City, Zip Code, State, Amount1,and Amount2</code>.  </p>

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

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

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

<pre><code>case  WHEN DenseRank(Sum(Sum([Amount1]) over [ZipCode]) over [State],"desc")&lt;6 then [ZipCode] ELSE NULL END
</code></pre>

<p>Any help would be great.  Thanks!</p>