Turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Want to count up when change occurs in either of two data columns

- 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

Aug 23, 2019 11:22 AM
(751 views)

So I have 2 columns of data that have many identical values but which increment at certain points. They are the month and date of data points. Example:

Month |
Day |
Desired Counter: |

1 | 1 | 1 |

1 | 1 | 1 |

2 | 2 | 2 |

2 | 3 | 3 |

3 | 1 | 4 |

3 | 1 | 4 |

Ive tried using lag unsuccessfully as I dont want an increment unless a value changes. I've tried different solutions including:

```
If(
Row() == 1, 1,
Lag( :Month, 1 ) & Lag( :Day, 1 ), Lag( :DesiredCounter, 1 )
)
```

A similar solution was posted but didnt work for me as I need to keep both columns into account. Can someone help with this? Thank you

1 ACCEPTED SOLUTION

Accepted Solutions

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

Here is a script that creates your sample data table, and applies the formula to the Desired Counter: column

```
New Table( "Example",
Add Rows( 6 ),
New Column( "Month",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 1, 2, 2, 3, 3] )
),
New Column( "Day",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 1, 2, 3, 1, 1] )
),
New Column( "Desired Counter:",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Row() == 1,
x = 1,
If( Lag( :Month ) != :Month | Lag( :Day ) != :Day,
x = x + 1
)
);
x;
)
)
)
```

It produces this data table:

Jim

4 REPLIES 4

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

Re: Want to count up when change occurs in either of two data columns

Here is how I typically do this:

```
If( Row() == 1,
x = 1,
If( Lag( :Month ) != :Month | Lag( :day ) != :Day,
x = x + 1
)
);
x;
```

Jim

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

Re: Want to count up when change occurs in either of two data columns

Hey Jim,

Thanks for your reply. So I tried this and I'm just getting a bunch of 1's still. Is there anything else I might need to add? X is our desired counting column right? Thank you

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

Here is a script that creates your sample data table, and applies the formula to the Desired Counter: column

```
New Table( "Example",
Add Rows( 6 ),
New Column( "Month",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 1, 2, 2, 3, 3] )
),
New Column( "Day",
Numeric,
"Continuous",
Format( "Best", 12 ),
Set Values( [1, 1, 2, 3, 1, 1] )
),
New Column( "Desired Counter:",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Row() == 1,
x = 1,
If( Lag( :Month ) != :Month | Lag( :Day ) != :Day,
x = x + 1
)
);
x;
)
)
)
```

It produces this data table:

Jim

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

Re: Want to count up when change occurs in either of two data columns

Oh, Im sorry, I thought I had to sub a column in for x. Nevermind, it works, thank you!!!