nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Formula help: Lag within a lag?

jswislar

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

Accepted answers

Jeff_Perkinson

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.

All comments

Mark_Bailey

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

jswislar

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.

mark_anawis

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?

jswislar

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

Mark_Bailey

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.

mark_anawis

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

jswislar

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.