Choose Language Hide Translation Bar
Highlighted
Level III

## Formula help: Lag within a lag?

I'm trying to count up occurrences within subgroups and I'm a bit stuck.

I have:

Site_ID, each site can have varying rows of data

P_code, the variable I'm trying to count

Index, my new variable

The data are sorted by Site_ID and P_Code.

I need a p_code count within site.

What I have so far is

If( :p_code != Lag( :p_code, 1 ), 1, Lag( :Index, 1 ) + 1 )

This counts the p_codes within sites the way I want, but it causes 2 problems. First, it makes the first line of data missing. Second, it sometimes mis-counts. For example, if Site #11 has the same p_code as the code in the last row for Site #10, Index increases by 1 instead of starting over again at 1. How do I correct this?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

## Re: Formula help: Lag within a lag?

Try a formula like this:

If(
Row() == 1, 1,
:Site_ID != Lag( :Site_ID, 1 ) | :p_code != Lag( :p_code, 1 ), 1,
Lag( :Index, 1 ) + 1
)

This says to put a 1 in the first row and then any time Site_ID or p_code changes put a 1, otherwise add 1 to the value of index from the previous row.

-Jeff
10 REPLIES 10
Highlighted
Staff

## Re: Formula help: Lag within a lag?

If you don't need the result in a data column, you might try using Tables > Summary or Analyze > Tabulate to get these totals.

Learn it once, use it forever!
Highlighted
Level III

## Re: Formula help: Lag within a lag?

I'd prefer them in a column because I'd like to pull out the Index=1 data for in-depth analysis. The current formula isn't counting correctly.
Highlighted
Level III

## Re: Formula help: Lag within a lag?

Have you tried using Tabulate platform with Site_ID and P_Code as rows to get a count of occurrences rather than creating a column with a formula?

Highlighted
Level III

## Re: Formula help: Lag within a lag?

Yes, that gives me the count, but I want to it in a column so I can examine these cases in more detail and not have to rerun tabulate
Highlighted
Staff

## Re: Formula help: Lag within a lag?

The Tabulate platform is interactive. It not only gives you the totals but you can click on one or more results and that changes the Selected row state in the data table. From there you can Tables > Subset or view the selected observation in any open plots through the dynamic linking feature.

Learn it once, use it forever!
Highlighted
Level III

## Re: Formula help: Lag within a lag?

Can you send part of your table as well as an expected output?

Highlighted
Level III

## Re: Formula help: Lag within a lag?

Sure. Like this:
Site_ID p_code Index
001 120 1
001 120 2
001 130 1
002 130 1
003 120 1
003 120 2
003 120 3

It's cases like site 002 that are giving me trouble.
Highlighted
Level III

## Re: Formula help: Lag within a lag?

Is Index the expected output that you want, that is the running count of your Site_ID and p_code?

Community Manager

## Re: Formula help: Lag within a lag?

Try a formula like this:

If(
Row() == 1, 1,
:Site_ID != Lag( :Site_ID, 1 ) | :p_code != Lag( :p_code, 1 ), 1,
Lag( :Index, 1 ) + 1
)

This says to put a 1 in the first row and then any time Site_ID or p_code changes put a 1, otherwise add 1 to the value of index from the previous row.

-Jeff
Article Labels

There are no labels assigned to this post.