Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 5, 2017 7:42 AM
(10286 views)

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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula help: Lag within a lag?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula help: Lag within a lag?

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.