Rank a list with groupings

[_dateter]

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. 

Input:
Customer ID / Product / Num
12345 / Widget 1 / 999
12345 / Widget 2 / 750
12345 / Widget 3 / 500
12346 / Widget 1 / 99
12346 / Widget 4 / 25
12347 / Widget 4 / 56
12347 / Widget 5 / 25

Desired Output:
Customer ID / Product / Num / Rank
12345 / Widget 1 / 999 / 1
12345 / Widget 2 / 750 / 2
12345 / Widget 3 / 500 / 3
12346 / Widget 1 / 99   / 1
12346 / Widget 4 / 25   / 2
12347 / Widget 4 / 56   / 1
12347 / Widget 5 / 25   / 2

[tom_montpool]

I can think of two ways to accomplish this. I will assume here that the data have been sorted already.

1. Formula & Running Total
   • Use a Formula tool to add a field filled with 1s (e.g. Rank)
   • Then use the Running Total tool configured to group by your Customer ID and to create the Running Total of the [Rank] field.
2. Multi-Row Formula
   • Use a Multi-Row Formula tool to create a new field called Rank using the expression "IF [Customer ID]==[Row-1:Customer ID] THEN [Row-1:Rank]+1 ELSE 1 ENDIF".

[_dateter]

Thank you! Both solutions make sense. I used the second option which worked great.

[_jalbertini]

Thanks for asking David and thanks for the answer Tom - this just helped me out yesterday.

Jon

[jarrod]

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.

[stephenweiss]

I've been using the running total and it seems to work great. My issue, however, is when there are ties.

I've tweaked the example to reflect that and I'm curious if you would know how to approach this problem so that the number you get is actually the same. Changes are highlighted below:

Input:
Customer ID / Product / Num
12345 / Widget 1 / 999
12345 / Widget 2 / 750
12345 / Widget 3 / 750
12346 / Widget 1 / 99
12346 / Widget 4 / 25
12347 / Widget 4 / 56
12347 / Widget 5 / 25

Desired Output:
Customer ID / Product / Num / Rank
12345 / Widget 1 / 999 / 1
12345 / Widget 2 / 750 / 2
12345 / Widget 3 / 750 / 2
12346 / Widget 1 / 99   / 1
12346 / Widget 4 / 25   / 2
12347 / Widget 4 / 56   / 1
12347 / Widget 5 / 25   / 2

[chris_love]

Hi Steve

The multirow formula is what you need here. Build a formula that checks the
previous row and adds 1 if its different to current row value, else stays
same.

I'll leave you to investigate but post if you need specific details or an
example.

Chris

[dawid_nawrot]

Multi-row formula worked for me.. thanks!

[Nifty]

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

//Else, assign value of 1.
ELSE 1
ENDIF

Cheers! 

[TomAdams]

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.

Any suggesions?