I want to rank a long list of records that contains groups and sub-groupings. The ranking would be based on each sub-group (e.g. 'Top 3 Products Purchased per Customer'). I think this should be fairly straight-forward but can't figure out how to do it. The data has three variables: Customer ID, Product Name, Number of Products Purchased. So for each unique customer id I need to sort and rank their top 3 products based on number of purchases.
I like both of those methods, however, the second one using the multi-row formula can be simplified to the "[Row-1:Rank]+1" formula and grouping by Customer ID within the tool. This will take care of the IIF function which checks for a new customer ID. Also, when using the Muti-Field tool, double-check that the "Values for Rows that don't Exist" is set to "0 or Empty" to ensure you don't produce any errors. The tool is set to this by default, but its always good to check.
The Multi-Row formula required an IF statement that considered both CustomerID and Num.
It also needed an additional "ELSEIF" statement that considered only CustomerID.
Below (and attached) is my solution:
//If the IDs of current & previous row are the same, AND the Num of widgets between current & previous row are the same, then do NOT increment the rank (no "+1") IF [CustomerID] == [Row-1:CustomerID] AND [Num] == [Row-1:Num] THEN [Row-1:RankDense]
//Else, if the IDs of current & previous row are the same (& implicit, Num of widgets is not), then increment rank by 1 ("+1") ELSEIF [CustomerID] == [Row-1:CustomerID] THEN [Row-1:RankDense]+1
Sorry to resurrect a pretty old thread, but I'm trying to find a way of doing this exact same thing, but ranking highest to lowest, rather than lowest to highest.
I've got various Categories with an varied number of transactions in each, and I need to get hold of just the two most recent within each category. As far as I can tell, the best way of doing this would be to rank the transactions, by category and by date from high to low, then filter on ranks 1 and 2 for each Category ID.