Formula application

[#1]

IF(MONTH(A151)=MONTH(A150),NA(),COUNTIF(W151:W181,1)*100/(COUNTIF(W151:W181,1)+COUNTIF(W151:W181,0))) . This is a formula applied in excel to create a new column, where Column A has date & column W contains only 0 & 1 as values. How to apply this same formula in JMP if we have columns A & W values.

[#2]

Your request is a bit unclear, but I think I am close to what you want - see attached file.  I created a column of random dates and then sorted them.  The W column is a random vector of 0 and 1 values.  The formula counts the fraction of 1 values in the next 30 days after a change in months.  If this isn't what you want, can you either explain it more clearly or post a simple example.  Remember, JMP is not a spreadsheet, so Excel formulas like CountIF don't always translate well.

[#3]

Thanks a lot for the reply. I applied this formula but this gives cumulative sum but in my case whenever month changes I need to to get the count of 0 & 1 for next 30 days

[#4]

Could you provide example table with all the needed columns + expected result column as JMP table. Makes it easier to understand the problem than trying to convert excel formula without any data to JMP Formula. You might just need something like

Col Cumulative Sum(:W, :A) or Col Sum(:W, :A)

[#5]

That's what I thought my example did - and it seemed to work in the file I attached.  I'd like to understand how it didn't work but your comment doesn't provide any useful information.  Similar to your initial request, you should provide some more detail so that we can understand what you want and how suggestions don't accomplish that.

[#6]

Hello- 
I had started to make a reply and then noticed another post.  This may be an alternate...

notes:

The month function will return the month portion of any date/timestamp coded numeric, same as Excel Month() function.
The IF will check to see if the current row is not on the boundary of month-  this is set to  return a JMP "Missing", which may behave a little differently than an Excel NA() .
Since the values were coded 1 and 0, just did an average of next 30 rows rather than {Count 1/(count 0 + count 1)}.   

When transitioning between spreadsheet oriented formulas and JMP data tables with formulas that act like Excel relative references (Lag, moving average)...

* examine the behavior at the top and bottom ends carefully to ensure intended operation*.  An Excel relative reference to "the row above" copied into the first row would return a #Ref! error, A JMP Lag(1) returns a missing in the first row.   Also look at the specifics of the JMP Rolling average as the number of remaining rows gets <30... (may be fine- just have your eyes open).  Can trap for this with an extra conditional in JMP formulas.. if (row()==1, special thing for first row, otherwise use lag function)

The description does not give rationale of the table design, but if the intent is to find the portion of 1 values within the current month and there is an assumption that there will be 30 rows per month sorted in order.... there is another approach that will be robust to # rows/month <>30 and row sorting...   With date/timestamp column A selected- right click>new formula column>date /time> "month year."...
then make a new formula column to find the "average" W for that month/year    Col Mean( :was_W, :"Month Year[was_A]"n ) * 100, similar conditional to find boundary row if needed

[#7]

Here is the way I would handle this.

The Loc() function return a matrix of all of the values in the matrix that match the second argument in the Loc() function.  Therefore

Give a matrix

 [1,0,1,1,.,5]

then

Loc( [1,0,1,1,.,5], 1 )

would return the matrix

[ 1, 1, 1 ]

so it is a simple matter of counting the number of rows in the matrix to find the CountIF

Length( Loc( [1,0,1,1,.,5], 1 ) )

which will return the value  3

Here is the complete formula

If( Month( :A ) == Lag( Month( :A ) ),
	.,
	wMatrix = :W[Index( Row(), Row() + 29 )];
	(Length( Loc( wMatrix, 1 ) ) * 100) / (Length( Loc( wMatrix, 1 ) ) + Length( Loc( wMatrix, 0 ) ));
)

Typically in JMP, one tries to avoid having calculation based upon a specific number of rows in the data table.  This current Discussion is a good example of this.  For the above formula to work correctly, the data table requires that exactly 30 rows are found after the change in month, to do the calculations on.  We all know, that different months have different numbers of days.  The following change to the above formula, shows how to do the calculations based upong finding all of the data for the current month, regardless of the number of days in the month

If( Month( :A ) == Lag( Month( :A ) ),
	.,
	wMatrix = :W[Current Data Table() << get rows where(
		Year( :A ) == Year( :A[Row()] ) & Month( :A ) == Month( :A[Row()] )
	)];
	(Length( Loc( wMatrix, 1 ) ) * 100) / (Length( Loc( wMatrix, 1 ) ) + Length( Loc( wMatrix, 0 ) ));
)