<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Refer to "Formula/Outcome" column which is my desired outcome. What formula can I use to create a sequence of numbers that restarts at each change in "composite key". In this example the "composite key" is ID and Category.
| ID | Category | Subcategory | Formula/Outcome |
|---|
| 101 | Working Conditions | Office temperature control | 1 |
| 101 | Working Conditions | Takes long to fix things | 2 |
| 101 | Supervisor | Lack of career development | 1 |
| 101 | Supervisor | Availability | 2 |
| 222 | Compensation | Rate is behind industry average | 1 |
| 222 | Supervisor | Lack of career development | 1 |
| 222 | Supervisor | Availability | 2 |
This type formula should work if the table is sorted:
If( Row() > 1,
If( :ID == Lag( :ID, 1 ) & :Category == Lag( :Category, 1 ),
Lag( :Outcome, 1 ) + 1,
1
),
1
)
The below should also work (but independent of sorting).
Eval( Eval Expr( Col Number( Row(), Row() > Expr( Row() ), :ID, :Category ) ) )
