How would you improve the Formula Editor?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
We're working on some improvements to the Formula Editor and we want your input.
What do you like about the Formula Editor and wouldn't want to see changed?
What bothers you?
What parts do you use most often?
What confused you the first time you used it?
What would make it more efficient?
How often do you double-click the formula and type the formula by hand?
We want your feedback regardless of how long you've used JMP. Whether you're a first-time user or longtime JMP jockey, your opinion is valuable.
Post your replies here. Please be as detailed as possible.
Thanks!
-Jeff
Re: How would you improve the Formula Editor?I've been thinking about this one for a while.
- Spread out the argument boxes for functions, especially if there are optional arguments. e.g. statistical functions like Col Mean: the little carat is nearly invisible on a 1080p monitor. I didn't know the By option existed for statistical funtions until a few years ago.
- Add an n-element rolling average function
- Is there a good way to create a library/add-in of user-created formulae other than old file > open > copy formula > paste?
- One way that I test if a formula is doing what I want it to do is to Analyze > Distribution or plot a run chart of the formula column. These would be good diagnostic options to add to the formula builder.
- I am always curious as to whether I wrote the best formula in terms of execution speed and memory usage. Can you provide guidance in the documentation?
Re: How would you improve the Formula Editor?Another option, new in JMP 12, is to select the columns of interest in the data table. Then right-click the header of one of them and select New Formula Column->Combine->Sum. This provides the option to select columns that aren't next to each other in the table by ctrl-clicking.
Re: How would you improve the Formula Editor?jason.paquette , indeed there is already a way to do what you ask.
Add the function Sum() to your formula. Click on the first column that you want to include and then shift-click on the last one. JMP will add all the columns in between.
Most of the functions in the Statistical list support this.
Re: How would you improve the Formula Editor?Hello,
One thing that I find would be useful would be a way to select multiple columns with the ability to select a function that will act on all of them.
i.e. say you have a wide dataset with 100s of columns and you want to get the sum of all of them without stacking the dataset.
Unless there is a way that I am not aware of, you need to insert each column one at a time separating them by clicking '+'. This would be a feature request that I would like, unless there is already a solution.
Thank you.
Re: How would you improve the Formula Editor?If you use EvalExpr() you can get around this.
x = 1;
y = 14;
Eval(EvalExpr(New COlumn("Formthing", formula(Expr(x)-(Expr(y))))));
Re: How would you improve the Formula Editor?One other thing that would be nice, when scripting in formulas through JSL, if I use a variable that was in my JSL code, it often simply gets sent to the formula as that variable. In the pasted view below, beta and eta are values determined in the JSL, then fed back to a column formula, which appears as,
What would be helpful is for these to either expand to their actual numbers, or have them appear as something that is selectable, or at least something that shows what the values inside the formula are at that moment. I have looked in the drop down that lists "parameters", but they do not appear there, so oftentimes, once the formula is written, it's tough to tell what the variable values are.
Re: How would you improve the Formula Editor?Add the PERT function to the formula editor (now you have to manually calculate the alpha and beta values and then use the BETA distribution)
Re: How would you improve the Formula Editor?At this moment, you can select whether to use Table columns, or table variables, constants, parameters, … through the drop-down menu at the very left corner of the Formula window. I guess the most widely used, is “Table Columns”. For me it would be very useful to have the other options (such as table variables, constants, parameters, ..) in a separate drop down menu, visually next to that of the Table Columns, so you can easily build a formula using table columns and table variables, without having to click on the drop down menu each time you switch from using a table column and a table variable.
And then a more general suggestion (which possibly goes beyond the formula editor): enable importing a list of table variables, eg data table with two columns (name of variable & value of variable); these variables are then added as table variables to your table. In this way you would have a clear overview of the variables/parameters you are working with, and can easily use these values for your calculations. Additionally, they should automatically update when you change the values in the initial table. Alternatively, the formula editor could allow using values from an external data table which lists all the variables (and then obviously, when changing the values in this external table, the formula editor also uses these new values).
Re: How would you improve the Formula Editor?I often have a frustration where I've got a fairly complex formula almost complete, then in editing the JSL of it I make a mistake, maybe a parenthesis is missing, and it just blots out the whole thing and I'm back to square 1 instead of getting a chance to edit the JSL I had there.
Re: How would you improve the Formula Editor?The formula editor is very important and helpful for my work in general.
I would appreciate more ease and flexibility for building formulas across several rows not just within the same row.
For repeated measures of variable length I would appreciate a function that collects the last value measured for each analysis unit (e.g.patient); this should be possible both in a horizontal and in a vertical arrangement of repeated data.
For complex formulas I would appreciate the possibility to store an interim result and then use this result in the final formula.
Thanks
Winfried
Re: How would you improve the Formula Editor?There's always a risk of confusing a user, but there are ways to mitigate those, for example, clearly displaying "Column Formula" in a prominent position.
Anyways, I would have the shortcut apply to everywhere in the column (cell, header, ect). I'm not sure why you would want different functionality to how a formula is applied based on whether you called the shortcut from a focus on a cell or from a focus on the column header. Doesn't make sense.
Currently, applying a column formula will overwrite the data in a column.
Re: How would you improve the Formula Editor?I didn't realize the right click gives me an option for Formula. That's actually pretty close to what I was thinking. Thanks, Jeff.
Re: How would you improve the Formula Editor?...to continue my variations on a theme of function additions:
Please consider adding a Mode function to the Formula Editor and available JSL functions.
Re: How would you improve the Formula Editor?Maybe a tool bar button/hot key/Cols menu item that creates a new formula column and pops open the editor all with one click might address the spirit of the request if I’m interpreting it correctly… or, maybe I’m just inserting my own wish here.
Re: How would you improve the Formula Editor?Good points, but do we risk confusing a user who may not understand the structure of a data table and that a formula applies to an entire column, not an individual cell?
What user interface element should have the keyboard focus when you type the equal (=) sign?
- An individual cell? What if the column has data in it but doesn't currently have a formula?
- The column header? This might work but if you've got to click to select it already, a right click and choosing Formula is pretty handy.
- Any other ideas?
As an aside, a little known feature is that you can type an = and a JSL expression in a cell and JMP will evaluate the expression and put the returned value into the cell. JMP doesn't store the expression, just the evaluated result.
Re: How would you improve the Formula Editor?From how I understood it, (and felt when I was first learning JMP) anyone coming from excel is use to clicking on a cell, typing "=" then plugging in their formula.
If "=" was a hot key, then it would be more intuitive for a new user (from excel) to click on a cell and hit "=" (which would pop up the formula editor) which they could happily plug in their formula.
Re: How would you improve the Formula Editor?I am going to give a suggestion for a slightly different take on the suggestion to add a hot key to get to the formula editor. If one rt mouse clicks on a column header, the Formula editor is just one click away. Therefore, adding a hot key to this method of getting to the Formula editor is no savings in key strokes.
Where the additional key strokes comes into play, is when one goes to the formula editor from the columns panel. You have to go through the Column Info panel first. Therefore, my suggestion is not for a hot key, to add Formula to the top level of the options available when one rt clicks on a column in the Columns Panel
Re: How would you improve the Formula Editor?A few more thoughts came to mind, so I wanted to throw them out there.
> Some functions come first, some arguments. For example, you type Word first. But for Match, you put the column in first. Be consistent.
> Some functions have strange ordering of arguments. Word comes to mind. You could fix or add in intelligence when possible. An example is Word. The 1st arguments is an attribute (word#), then comes the column. I've seen people get that backwards a million times.
> While I'm on Word (which I LOVE that function), hidden arguments need to be more obvious (like delimiters in this case).
> Need a keyboard equivalent for concat. Do that a lot & hate having to take hands off the keyboard to get the function. (Also, not obvious at all that hitting "," multiple times gives you a bunch of concats - a completely hidden gem).
> Auto fill on functions & colukns is a GREAT idea. I'd do it like programming languages do where a small srop down pops up as you type & you can down arrow to select the appropriate one.
> Local variables are great, but why only nested? Love to have that in a linear format.
Re: How would you improve the Formula Editor?As a new user, there are a few of things that I believe would make the formula editor quite superior:
1) A way to save the history for a given formula. Function should be automatic [by time] and user-activated [a save to history button]
2) Extended record for CMD+Z [Undo or re-do], currently, it only goes back to your very last change
3) An option to export formula to current script editor
3) An option to subscript abbreviations of variables [see attached figure] [I would be so happy if I knew how to do this ^_^ ]
4) A shortcut to open the formula editor from a column. Secondary click, then Formula is a short path, but in the world of CMD+C, CMD+V, CMD+[something] to see the formula of a selected column would be helpful
5) Shortcut to orient the formula horizontally or vertically
And these are just some improvements that as a new user would like to see.
And something that I would love to see in general is a Script translator [JMP<=>R<=>Matlab]
-Jose GL
Re: How would you improve the Formula Editor?I would also, very much appreciate this. This is the best suggestion I've seen so far.
Re: How would you improve the Formula Editor?I'm not yet a regular user, so this may already be available. I wish there was some kind of hotkey to launch the formula editor. Say, select the column and hit '='. I've always found the 2-3 mouse clicks it takes to get to the formula editor slightly annoying.
Re: How would you improve the Formula Editor?I can't replicate the "^" effect either. I only get X^2.
Re: How would you improve the Formula Editor?1. First of all, making the default formula user interface larger would be a big step. I don't believe that size hasn't changed since ancient times even though nowadays screens are larger and/or have a much higher resolution.
2. I really like the suggestions of having JMP assemble the formula from individual columns. I usually have student test individual components of a larger formula before assembling the final product.
3. Give the user the option in preferences to select function groups he/she most often uses.That would cut down on the clutter. In the process of creating that capability you could create more function groups. For teaching it would really help if students didn't have to wade through the muck of 100+ functions to get to the one they really need.
Re: How would you improve the Formula Editor?i get the same using Jmp11
Re: How would you improve the Formula Editor?Hi Jeff,
it is constantly happening on my system. But it might be due to my setup (using Windows 7 in paralells on a Mac).
Sebastian
Re: How would you improve the Formula Editor?That does sound like it would be annoying, however, I'm unable to reproduce that behavior. It happens every time?
Is anyone else seeing that?
-Jeff
Re: How would you improve the Formula Editor?Another thing: Whenever I need to calculate the square of something I intuitively press the "^"-button on my keyboard.
The formula editor automatically makes this out of it: (:X ^ 2) ^ 2
My expectation would be :X^2 only.
And yes: I am pressing the "^"-key only once 
Re: How would you improve the Formula Editor?I guess it's more a power-user thing, but I struggle from time to time with some functions that use multiple arguments but the arguments are not named.
Example:
Using the "word" function in the "character"-section there are the names of the arguments displayed in gray inside the boxes (n and text).
This is not the case for the "substitute" function.
It would be great if all functions displayed the argument names.
Re: How would you improve the Formula Editor?One of the most useful functions, I find is "Match" and ability to auto populate with values in a column is great. It usually takes 4-5 tries to get the exact sequence of keystrokes with correct modifiers to work. I would love to see that one made a bit easier.
Re: How would you improve the Formula Editor?1) When I was first learning JSL I would use the formula editor to guide some of my script, would have been nice to have a "save formula script to script window" option.
2) If a task can be accomplished by more than one function, after the user creates one version, could JMP recommend alternatives?
Re: How would you improve the Formula Editor?I would like an undo button as I would use it often. I know I can go to Edit>undo or use cntrl-z however, I would like an undo on the formula editor window.
Re: How would you improve the Formula Editor?Perhaps add a preview tab or window where you can see the results of the formula readily. Might be nice for a new user who has to iterate a few times on a formula to get it right. This is in query builder and it is awesome to have sometimes!! Graph builder gives immediate feedback also making it easy to iterate quickly on a graph construction. Formula builder is a little more clunky to me - hit apply look at table click back over to formula editor etc.
Clicking on boxes was goofy to me in my first experiences. It was easy to click too many times and get the formula text. If I was spastic sometimes i would do something to the text accidentally. Honestly it still is a little frustrating sometimes.
I really like being able to see the formula. Generally this is really cool. If there is a way to clean up how the formula are displayed that would be awesome. Auto sizing is good and bad. The good for me is that it keeps things compact and I have the option to make things bigger. New users may not readily see this the same way.
I too like the excel comments as most new users I work with have a lot of experience in excel. I would add that making it more intuitive than excel would be a great mark to hit. Excel isn't exactly intuitive but people are used to it. I would love it if an excel user saw this and thought wow I doubt I will be using excel much anymore
Re: How would you improve the Formula Editor?For new users it would be helpful to have a dedicated formulas tutorial under the help menu. I think understanding the link between setting up columns and the profiler would be really great as an beginner/intermediate exercise. You can get so much more out of the formula with profilers.
Re: How would you improve the Formula Editor?I was thinking something very similar Jim! Sometimes formula get complex and messy in the formula editor. I often write a script in these cases because it is just easier for me to see what I am doing.
Maybe you could call formula editor formula builder (like the other interactive builders -sql, graph) and have an entirely different option for formula script
Re: How would you improve the Formula Editor?Provide the ability to copy the formulas in columns with the formulas incrementing the references as will happen with Excel without absolute addresses. For that matter, include the ability to have absolute column addresses.
Re: How would you improve the Formula Editor?Was recently teaching someone who is colorblind how to use the formula editor. He couldn't see the red box (looked gray to him). I can't imagine trying to use this tool w/o knowing where you are editing. Add platform option to change the editing box thickness/color/flashing would be very helpful.
Re: How would you improve the Formula Editor?Hi All,
Overall, the Formula Editor is pretty good the way it is. But there are a few issues that would be nice to fix:
- Conditional Formulas (Match and Choose) with multiple conditions: the Else statement at the end gets lost when adding and removing terms
- The y^x formula default to 2^x is not ideal since most of the time I'm interested in elevating a variable to a given power
- The Row State functions are particularly unintuitive. Example: Color Of (X) returns nothing if X is not a Row State Column but Color Of () returns correctly the numerical value of the row color.
About possible additions:
- Add False Discovery Rate transformation function similar to Col Mean, Col Sum etc.
- Enable the entry and edition of the same formula across multiple columns using a range of variables (i.e. columns): Example: Column 4 = Log (Column 1), Column 5 = Log (Column 2), Column 6 = Log (Column 3) etc...
- Enable grouping of favorite / most used functions into a separate category in the Menu
Re: How would you improve the Formula Editor?I agree with msharp. I and many other folks I know who first attempt a formula in SAS JMP think in terms of the sequence left to right. Also, the more "Excel" like the easier the translation and ramp up on the learning curve.
Reading the other comments, you have some great ideas for enhancements.
Good luck on the next gen.
Re: How would you improve the Formula Editor?For me, I feel the buttons are un-intuitive.
As a beginner, you tend to push the buttons instead of typing out the formula. Say I want Mean(Col1)-Mean(Col2), I would push the buttons as I read the formula, thus: Push button mean, then Col1, then minus, then mean, and finally Col2. Since that's how it reads, one would figure that's how you would push the buttons. However, this sequence of events leads to Mean(Col1 - Mean(Col2)) not Mean(Col1)-Mean(Col2).
In order to get the desired formula, a beginner would have to: Push this sequence, Mean, Col1, select the formula and make sure everything is highlighted, Minus, Mean, Col2. That middle portion is really un-intuitive.
Re: How would you improve the Formula Editor?Hi Jeff,
How about listing the content under the menus in some logical manner.
Alphabetical order could help newcomers find their way around or alternatively somehow grouped by function.
- Philip
Re: How would you improve the Formula Editor?All of these are great suggestions for power users.
What about for first-time or casual users?
What would make the formula editor easier to figure out?
What confuses you about the formula editor?
Thanks!
-Jeff
Re: How would you improve the Formula Editor?Please please please add the Robust Mean and Robust Standard Deviation functions to the Formula Editor and to available JSL functions!
Re: How would you improve the Formula Editor?
When building long and complex formulas (with 2000+ terms), I start with multiple intermediate columns with formulas. In this case it would be extremely helpful to do a wholesale replace of the column reference with the formula in that column in the formula editor window. In other words, I am suggesting you give the ability to collapse sequential formula columns into a single column.
I often use the feature of copy and pasting formulas between a script window and the formula editor.
Re: How would you improve the Formula Editor?Building on Jim's thoughts - how about if the JSL editor does more auto-completion of built-in functions, like you get in Visual Studio or when entering an Excel formula?
Re: How would you improve the Formula Editor?It would be nice if when you double click on the formula to go into editor mode, that a small jsl window, with all of it's functionality popup.
There needs to be a way to access all of the elements of a function without having to go into the editor mode. An example of this is the Worrd() function. The delimiter element has to be manually added.
when selecting a function, there should be a button beside each function that when clicked goes directly to the Scripting Index help for that function
There needs to be more space between the elements in the picture version of the formula, so that selecting of the different elements within the formula isn't sooooooo difficult
translator from Excel functions to jmp functions.
I use the manual editor a lot
Re: How would you improve the Formula Editor?Using Parameter() in formula editor for nonlinear modeling.
If you try to edit parameters by hand after the formula has been made, you have to go to parameters and right click->edit. A single left click will replace your whole formula with just that parameter. Not the end of the world to press ctrl+Z, just a minor annoyance.
Re: How would you improve the Formula Editor?add in an autofill for the column or function names. it can be a pain to keep scrolling to find the right column, and typing it can be tough if you forget how it is spelled. It would be nice if there was a feature that would autofill the possible columns or functions based on what you typed. Sort of like what Google does with autofilling a search, except the only possibilities are the column headings or function names.
Re: How would you improve the Formula Editor?some tables have a long list of variables. it would be nice to have the Filter Col Selector in hand when generating a formula. This feature is available in the distribution and the fit Y by X platform and is very useful.
By the way, it would be good to add it to the fit model platform and actually any platform.
Re: How would you improve the Formula Editor?Some of my formulas are too complex, so I double-click on the window and type the formula in by hand. Initially the window we're given to enter a formula in is too small, and the font is too small as well. (Must be getting old). I would suggest making it bigger.
