742934_en-US

liliconcom

Formula application

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.

Advanced Statistical ModelingRe: Formula application

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.

Re: Formula application

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

 

Re: Formula application

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)

Re: Formula application

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.

Re: Formula application

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