Subscribe Bookmark RSS Feed

Incrementally count events

mecdks

Community Member

Joined:

Sep 16, 2013

I've got a log containing events in the life of magazine subscriptions. Each row includes a customer number, the type of event (new subscription, change subscription, cancel subscription, etc), the number of subscriptions included in the event and a date. Some customers have multiple subscriptions simultaneously, sometimes with different start and end dates. My end goal is to be able to determine which subscriptions were active at the beginning of each month. As one step towards that goal, I want to create a variable that counts up each time the customer has a particular type of event.

In Excel with 250 rows of data, I'd sort my rows chronologically and use:

=COUNTIFS(A$1:A250,A1,B$1:B250,B1)

and allow the fixed start point to give me a running count of rows where the two variables are a match. Unfortunately, the real dataset has too many rows for Excel. How can I accomplish this in JMP 10?

1 REPLY
ms

Super User

Joined:

Jun 23, 2011

If I understand the problem correctly I think this column formula should work:

Sum( :A[Index( Row(), Col Number( :A ) )] == :A & :B[Index( Row(), Col Number( :B ) )] == :B )

A and B are here the column names of the two variables.