Subscribe Bookmark RSS Feed

How do you enumurate rows at each change in level of a categorical variable?

daniellopez46

Community Member

Joined:

Mar 3, 2015

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.

IDCategorySubcategoryFormula/Outcome
101Working ConditionsOffice temperature control1
101Working ConditionsTakes long to fix things2
101SupervisorLack of career development1
101SupervisorAvailability2
222CompensationRate is behind industry average1
222SupervisorLack of career development1
222SupervisorAvailability2
1 REPLY
ms

Super User

Joined:

Jun 23, 2011

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 ) ) )