Option for formulas to ignore excluded rowsI would like to see an option for formulas to ignore excluded rows without having to build this into the formula using the "exclude state" formula function. I think the easiet way for this to be implemented is to have it as a column property option in the formula column property option box
So just one more check box here that would say "treat excluded rows as missing"
Thanks for considering this request.
Steve
Mass CustomizationRe: Option for formulas to ignore excluded rows - Status changed to: InvestigatingThanks for all the discussion @hogi @shampton82 ! We are reviewing the new information you have added and will update this request once we have prioritized.
Re: Option for formulas to ignore excluded rowsHi @shampton82 .
OK from my side 
For me, some support in handling Excluded States in JSL expressions would be OK, e.g. adding a switch to the right click menu to add group by excluded automatically to auto-generated col aggregation expressions:
group by excluded()
This will be soooo helpful - I hope Jmp team will implement it in the next version.
In addition: Users should be warned if there are excluded rows and if the Formula doesn't account for that.
No user should be "left alone" with wrong result - and nobody telling him that they are wrong.
Many Jmp users are definitely aware of this issue (it's the FIRST thing I tell colleagues who start using Jmp) - but I am sure there are also many users who are not aware
-yet.
The checkbox you describe - wonderful !!!
For sure, enabling the checkbox could have severe speed issues - as any row state change will trigger new evaluations.
And for my feeling changing row state settings takes forever!
So a checkbox would be exactly the right thing - and perhaps a setting in the preferences where the user can decide if he wants to have the smooth auto-respect-exclude variant or the fast ignore-excluded states enabled by default.
Re: Option for formulas to ignore excluded rowsHey @hogi , sorry i missed your reply!
Yeah it can get complicated fast and I've had some frustrating times with it. That's why a button on the column properties would be a great win for the users.
I see you posted something along these same lines as needing to address this issue, maybe we should link our post?
Steve
Re: Option for formulas to ignore excluded rowsI fear, for more complex formulas, it will not be 100% clear where to put the Excluded, right?
It could be added as an additional argument to any Col ... function in the formula.
Are there additional paces where it is necessary?
e.g in get rows where, add
Excluded()[row()]==0
Re: Option for formulas to ignore excluded rowsYeah, here is an example. Lets say in the Titanic example data set I make this formula.
and I am looking at this chart
I decide that I have a bunch of data that isn't useful in this analysis and is skewing the mean so I exclude it. The chart changes but my calculated value did not in the formula and thus the mean dots and the caption box summary statistic did not change:
So in this case I would want the formula to treat the excluded rows as missing because the excluded data is not useful in this analysis. However, I may want to keep it for another analysis and thus recoding it to missing is not a great option either.
My work around has been doing something like this
but it has not always been this straight forward.
Hope this helps!
Steve
Re: Option for formulas to ignore excluded rows - Status changed to: Needs InfoHello @shampton82. Thank you for the request! I am hoping you can provide some example use cases of when this feature would be helpful?
Re: Option for formulas to ignore excluded rows - Status changed to: InvestigatingRe: Option for formulas to ignore excluded rows - Status changed to: AcknowledgedHi @shampton82, thank you for your suggestion! We have captured your request and will take it under consideration.
Re: Option for formulas to ignore excluded rowssimilar wish:
Formula Editor: meaningful results with excluded rows
Re: Option for formulas to ignore excluded rows - Status changed to: AcknowledgedRe: Option for formulas to ignore excluded rowsThis would be great for formulas and for curve fitting!
Re: Option for formulas to ignore excluded rowsMy 2-cents, I'm a relatively new JMP user and was about to submit this as a bug to tech support. I thought there was no way excluded rows should be included in formula results. That seemed to be the main point of excluding them. That to me was what differentiated Exclude versus Hide.
I suppose a check box for each column would be better than nothing, but in my opinion the default should be that all excluded rows are excluded from everything. Maybe a table-wide setting would be more straightforward.
Regardless, since one previous commenter noted that this 'gotcha' was one of the first things they warn new JMP users about then it's probably not the ideal approach and worthy of being addressed sooner rather than later. I will need to go back through everything I've built in the last few months and see if I made any ill-informed decisions based on the intuitive yet incorrect assumption of excluded row behavior.
Re: Option for formulas to ignore excluded rows@MeanChris most important: please support the wish with a vote : )
unfortunately, comments don't count.
Up to now, this wish has 4 Kudus.
For the Developers this is a clear sign that just a minority of the users has issues with the current behavior -> nothing to be fixed.
Why a minority:
The users have to be in a narrow time corridor between not being affected and not being vulnerable anymore.
From my point of view,
- a user needs some experience to suffer from the bug (uses who don't know Column Formulas: they are safe)
- a user needs some experience to notice the bug (any user expects a default mode like in Graph Builder - if you don't expect the bug the chance is lower that you notice it)
- on the other hand, a user needs some "bad luck" - most users at this stage learned from a colleague : this is very dangerous!
To protect users in this intermediate level, I set up a community post to collect such Places where Jmp does something unexpected.
Here is another post with the Tiny Traps in Jmp and JSL .
Re: Option for formulas to ignore excluded rowsWith JMP19, there will be scoped formulas which will fix the issues for many applications.
For many cases, but not all cases:
At the moment, scoping is just planned for Transform Formulas in Reports.
The same approach *) could fix the issue with Column formulas as well.
*) via: column formulas with default option @excluded
Re: Option for formulas to ignore excluded rows@hogi Thanks for the link and the 'voting' tip. I didn't realize it was the Kudos count that mattered most. I'll use my new power responsibly.
Re: Option for formulas to ignore excluded rowsI have the same issue and just tried numerous approaches around it. None of them worked.
Re: Option for formulas to ignore excluded rowsHi @babn , in this post Caution: Places where Jmp does something unexpected
I collected some approaches (just ones that work).
The easiest approach:
Col Mean(:height, excluded())
The disadvantage:
excluded rows get "their own result".
Re: Option for formulas to ignore excluded rows7 Kudos - not a lot, but very much looking forward to have this resolved.
Re: Option for formulas to ignore excluded rowsI was trying to determine if a dimension is meeting tolerance and I have lots of excluded rows. This work around leaves excluded rows as blanks.
If(
Excluded(), .,
:OUTTOL == 0, 0,
1
)
Re: Option for formulas to ignore excluded rowsAgree.
Col Mean (
If( Excluded(), .,
:OUTTOL )
)
On one hand, slightly more complicated than using excluded() as a grouping variable.
But without the disadvantage of:
excluded rows get "their own result".
