Subscribe Bookmark RSS Feed

Formula help: Lag within a lag?

jswislar

Occasional Contributor

Joined:

Nov 17, 2016

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
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

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
)

JMPScreenSnapz069.png

 

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
markbailey

Staff

Joined:

Jun 23, 2011

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

Occasional Contributor

Joined:

Nov 17, 2016

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

Community Trekker

Joined:

Nov 18, 2014

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

Occasional Contributor

Joined:

Nov 17, 2016

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
markbailey

Staff

Joined:

Jun 23, 2011

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

Community Trekker

Joined:

Nov 18, 2014

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

jswislar

Occasional Contributor

Joined:

Nov 17, 2016

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.
mark_anawis

Community Trekker

Joined:

Nov 18, 2014

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

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

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
)

JMPScreenSnapz069.png

 

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