Please see the demo file covering number of scenarios showing how to Rank all values or aggregated values; or rank on more than one field. Some of the formulas used: Rank function Syntax: RANK(value, field, isAscending, includeNulls, dataSubset)
Ranking Spend= RANK([Spend]) # simple Rank comparing all values in the Spend field
Rank Spend per Week= RANK([Spend], [Spend], false, false, SUBSET([Week])) #Ranking Spend values inside each Week
SubRank Clicks = RANK([Clicks], [Clicks], false, false,subset([Rank Spend per Week] )) # secondary rank field, based on the Clicks value. Meaningful when multiple rows share the same [Rank spend per Week] value
Spend per Keyword= SUBSET_SUM([Spend], SUBSET([Keyword])) # total Spend for each Keyword
[One]=1 # basis for running total indexing
Running total per Keyword: RUNNINGTOTAL([One], SUBSET([Keyword]))
Rank Totals per Keyword= IF([Running total per Keyw]>1, null, RANK([Spend per Keyword], [Spend per Keyword], false, true, SUBSET([Running total per Keyw],1,"="))) # ignoring the values greater than 1, rank compares only the subset sum values where [Running total per Keyw] is 1, ensuring that only one value per category is compared.
Just wanted to say thanks for putting this together - was having a problem earlier today with this function and this has helped me figure out what was wrong (needed to change the aggregation function from 'sum' to 'formula result').