Formulas in JMP

In today’s issue of JMP in Action, Jeff Upton shows you how to interactively create formulas in JMP to get extract additional knowledge out of your data. Using JMP’s Formula Editor, you’ll learn how to create transformed or derived variables using built-in functions, constants and/or existing variables. Jeff also shows us how to use the Formula Editor to quickly and easily build formulas using drag and drop features and how to store and use local variables for creating future formulas.

Here are some links to additional materials referenced during the segment.

JMP Help: Formula Editor

Using Formulas to Get the Most from Your Data - Journal and Files

Full Transcript (Automatically Generated)

everybody, my name is Jeff Upton. I'm a systems engineer out of San Francisco serving our Bay Area users. And this is your jumping action segment for today. So today we're gonna be talking about formulas. And when I first started using jump, I came from a long history of a spreadsheet kind of applications. So think of your favorite spreadsheet. So the first thing I did when my boss said, Hey, I want you to build the formulas was I went to my spreadsheet and I said, Okay, let's let's build this just like I did in previous spreadsheets. That's not the best way to build formula. It does work in this case, use jump as a nice speculator. That's not what we're going to be talking about. today. We're going to be talking about jumps Formula Editor. And to kind of guide this conversation. I'm going to start with one of my favorite jump data tables. This is a tablet manufacturing process, out of the pharmaceutical industry. Really, all you need to know about this process is that we have so inputs, and we have an output. And we're going to try and come up with some relationships between those inputs and those outputs.

So one of the first things I would like to show you all is that you don't actually need to create a column property to create a formula. So if I'm in graph builder, I'm exploring this data sets. There's actually the ability within pretty much most of these jump platforms. When you have these column dialog windows, you have the ability to right click on these variables, and you can create formulas directly using these variables. So in this case, you know, the solution here is our Y variable of interest.

And maybe I have some hypothesis about temperatures for one of my unit operations within this tablet manufacturing process. And so I might want to take a ratio of the inlet temperature to the exhaust pressure. And so if I right click here, just from within this jump, gooey, cool user interface have the ability to combine these variables and take a look at the the ratio of the inlet temperature to the outlet temperature. So I'm gonna click on this, this makes what what I like to call a temporary variable. So you can see this is italicized, and so I can play around with this within my jump interface. My hypothesis is wrong. I don't know if there's any real correlation between this ratio and dissolution. But the point is here that I've created this formula, and if I wanted to add this formula to my table, all I need to do again is to right click, and I can add this table. So let's take a look at that.

Soon I went ahead and I added that column. Now I've got this nice exotic inlet to exhaust temperature ratio. You'll notice to the side of that column, we have this plus icon now this indicates a fault. And if I want to real quickly edit like it, click on that, and this gives us this formula builder. Now this is the this is the workhorse For when you're creating formulas in jump, I want to take a couple minutes just to go through the the interface. So for those of you that are brand new to jump this is, this is your formula builder 101. For those of you that are longtime jump users, maybe you'll pick up something something cool here, you will notice the business end of our, of our formulas right here. So this is really, it's very similar to a graph builder. It's a drag and drop interface. If I wanted to, you know, replace this inlet temperature variable with a different variable blend time, it's not really gonna make sense here, I can just kind of drag and drop that and replace that variable there.

You'll also notice there's a slew of very commonly used functionality appear at the top bar. So some basic arithmetic kind of functions, as well as some more sophisticated functions are going to help you to build a formula. So if I had no idea, you know, what is this little icon right here. If I hover over this jump, it's going to have some hover help. That tells me that this helps me too. Peel off the function. What does that actually mean? Well, I click that. And okay, I'm peeling away that variable that I just added. I'll go ahead and do that because I want to keep that there. So this is a, this is a formula that we created in graph builder. But how do we actually create our own brand new formula out, you know, using any of those kind of simple point and click kind of techniques. So I'm going to do within jump, I'm going to go to my gray space, I'm going to create a new column. When you right click here, a formula is a column property. However, it's such a common, such a popular column property that we've added it straight here to the window here. So when I click on this formula button, you'll notice we have all of the columns that are available on our data table. These are what we're going to use to build our formulas. We have a whole slew of different functionalities that you have within jump. So one thing that I really like to show off another thing from 10 years ago when I first started working dramatically with jumping with spreadsheets, conditional statements. Now I know I have this conditional menu right here. If I wanted to build an if then statement, I could just select that there. We also have this nice filtering capability though. So you don't need to know where everything lies right away. So if I were to type in an if statement here, you can see all the different places where it shows up. So I'm going to create an if statement to figure out whether or not my lots of aspirin in this or tablets in this example, were accepted or rejected. I could then go ahead and jump is going to offer me up

some advice as far as how I can build this statement. And so this is going to be a simple comparison, I'm just going to check if my dissolution is greater than you can even type this in. I could also have cleared this filter and gone through here to a comparison tool. I went ahead and just type that in. My dissolution is greater than 70. Then I'm going to say this is accepted. I'm going to add some quotes here. That way jump knows this is going to be a character string. Otherwise, get ahead of myself. Otherwise, we're going to reject those lots.

Now, one thing that used to drive me nuts when I was creating spreadsheets was that these if statements, I would create these nested if statements. So I would have if followed by parentheses, and then I'd have all these long string in jump, you don't necessarily need to do that, we're going to use another one of these tools up here. tool, you can actually go ahead and if I wanted to create a nested if statement, I could just start clicking away and I can add additional kind of, you know, if then kind of statements here. So pretty useful. These functionalities here, pretty much anything that you can do on your spreadsheet applications, we have those functions available to you here. Last piece that I want to show off, has to do with this little functionality right here. So this is where we store a number of different types of variables. Typically you'll start with a constant menu right here. So you've got just variable various commonly used constants that you might be interested in applying to your formulas. What I like to also show off though, are these table variables and local variables. So table variables, they come from your actual table itself. So if I wanted to create a table variable, here you see a fudge factor. This is a variable that I might want to use and numerous different formulas. So if I click this red hotspot here, create your table over here. Maybe this is a cost of good. And you can have your cost of goods there. Now when I create a formula column, I have that table variable available to us. And so that might be maybe let's just throw a variable in here screen size. We might want to multiply that times a your cost you're good. So you're To use those local variables, or in this case, your table variable. Here, local variables work very similarly, although they're going to be restrained to this particular formula. So you can create a new formula here, this might be just a test variable. Now we can use this test variable in lieu of the cost of goods. And the last item in this list is a little bit unique. It's a parameter for that, I'm going to really quickly jump over to a different data set. You've seen in the news a lot lately, people are doing a lot of forecasting. In this situation, we're modeling population growth over time.

So if we take a look, this is our nonlinear platform. What we've got here is some population data. And then we've got a function that we're trying to fit to that population data. So if we take a look at this formula, we're trying to apply an exponential fit to to this particular set of data. You'll notice that now we have some parameters here. So parameters are unique and have local variables that you use within a formula that interact with certain platforms. So for in this case, these two local variables, local parameters, v zero and v one correspond to variables that we're trying to find that we're trying to fit to this particular curve in our data set. So we have starting to use if we hit the Go button, and just do our best fit there, and then we can save these variables back out to our data table. formula here now, jumps nonlinear platform has interacted parameter settings here and up to those parameter setting. Okay, typically on this segment, we take some time for questions. I don't see anything in the q&a then. So I'm going to try and keep the size on time as possible. That's action for today and Julian, back to you.

everybody, my name is Jeff Upton. I'm a systems engineer out of San Francisco serving our Bay Area users. And this is your jumping action segment for today. So today we're gonna be talking about formulas. And when I first started using jump, I came from a long history of a spreadsheet kind of applications. So think of your favorite spreadsheet. So the first thing I did when my boss said, Hey, I want you to build the formulas was I went to my spreadsheet and I said, Okay, let's let's build this just like I did in previous spreadsheets. That's not the best way to build formula. It does work in this case, use jump as a nice speculator. That's not what we're going to be talking about. today. We're going to be talking about jumps Formula Editor. And to kind of guide this conversation. I'm going to start with one of my favorite jump data tables. This is a tablet manufacturing process, out of the pharmaceutical industry. Really, all you need to know about this process is that we have so inputs, and we have an output. And we're going to try and come up with some relationships between those inputs and those outputs. So one of the first things I would like to show you all is that you don't actually need to create a column property to create a formula. So if I'm in graph builder, I'm exploring this data sets. There's actually the ability within pretty much most of these jump platforms. When you have these column dialog windows, you have the ability to right click on these variables, and you can create formulas directly using these variables. So in this case, you know, the solution here is our Y variable of interest. And maybe I have some hypothesis about temperatures for one of my unit operations within this tablet manufacturing process. And so I might want to take a ratio of the inlet temperature to the exhaust pressure. And so if I right click here, just from within this jump, gooey, cool user interface have the ability to combine these variables and take a look at the the ratio of the inlet temperature to the outlet temperature. So I'm gonna click on this, this makes what what I like to call a temporary variable. So you can see this is italicized, and so I can play around with this within my jump interface. My hypothesis is wrong. I don't know if there's any real correlation between this ratio and dissolution. But the point is here that I've created this formula, and if I wanted to add this formula to my table, all I need to do again is to right click, and I can add this table. So let's take a look at that. Soon I went ahead and I added that column. Now I've got this nice exotic inlet to exhaust temperature ratio. You'll notice to the side of that column, we have this plus icon now this indicates a fault. And if I want to real quickly edit like it, click on that, and this gives us this formula builder. Now this is the this is the workhorse For when you're creating formulas in jump, I want to take a couple minutes just to go through the the interface. So for those of you that are brand new to jump this is, this is your formula builder 101. For those of you that are longtime jump users, maybe you'll pick up something something cool here, you will notice the business end of our, of our formulas right here. So this is really, it's very similar to a graph builder. It's a drag and drop interface. If I wanted to, you know, replace this inlet temperature variable with a different variable blend time, it's not really gonna make sense here, I can just kind of drag and drop that and replace that variable there. You'll also notice there's a slew of very commonly used functionality appear at the top bar. So some basic arithmetic kind of functions, as well as some more sophisticated functions are going to help you to build a formula. So if I had no idea, you know, what is this little icon right here. If I hover over this jump, it's going to have some hover help. That tells me that this helps me too. Peel off the function. What does that actually mean? Well, I click that. And okay, I'm peeling away that variable that I just added. I'll go ahead and do that because I want to keep that there. So this is a, this is a formula that we created in graph builder. But how do we actually create our own brand new formula out, you know, using any of those kind of simple point and click kind of techniques. So I'm going to do within jump, I'm going to go to my gray space, I'm going to create a new column. When you right click here, a formula is a column property. However, it's such a common, such a popular column property that we've added it straight here to the window here. So when I click on this formula button, you'll notice we have all of the columns that are available on our data table. These are what we're going to use to build our formulas. We have a whole slew of different functionalities that you have within jump. So one thing that I really like to show off another thing from 10 years ago when I first started working dramatically with jumping with spreadsheets, conditional statements. Now I know I have this conditional menu right here. If I wanted to build an if then statement, I could just select that there. We also have this nice filtering capability though. So you don't need to know where everything lies right away. So if I were to type in an if statement here, you can see all the different places where it shows up. So I'm going to create an if statement to figure out whether or not my lots of aspirin in this or tablets in this example, were accepted or rejected. I could then go ahead and jump is going to offer me up some advice as far as how I can build this statement. And so this is going to be a simple comparison, I'm just going to check if my dissolution is greater than you can even type this in. I could also have cleared this filter and gone through here to a comparison tool. I went ahead and just type that in. My dissolution is greater than 70. Then I'm going to say this is accepted. I'm going to add some quotes here. That way jump knows this is going to be a character string. Otherwise, get ahead of myself. Otherwise, we're going to reject those lots. Now, one thing that used to drive me nuts when I was creating spreadsheets was that these if statements, I would create these nested if statements. So I would have if followed by parentheses, and then I'd have all these long string in jump, you don't necessarily need to do that, we're going to use another one of these tools up here. tool, you can actually go ahead and if I wanted to create a nested if statement, I could just start clicking away and I can add additional kind of, you know, if then kind of statements here. So pretty useful. These functionalities here, pretty much anything that you can do on your spreadsheet applications, we have those functions available to you here. Last piece that I want to show off, has to do with this little functionality right here. So this is where we store a number of different types of variables. Typically you'll start with a constant menu right here. So you've got just variable various commonly used constants that you might be interested in applying to your formulas. What I like to also show off though, are these table variables and local variables. So table variables, they come from your actual table itself. So if I wanted to create a table variable, here you see a fudge factor. This is a variable that I might want to use and numerous different formulas. So if I click this red hotspot here, create your table over here. Maybe this is a cost of good. And you can have your cost of goods there. Now when I create a formula column, I have that table variable available to us. And so that might be maybe let's just throw a variable in here screen size. We might want to multiply that times a your cost you're good. So you're To use those local variables, or in this case, your table variable. Here, local variables work very similarly, although they're going to be restrained to this particular formula. So you can create a new formula here, this might be just a test variable. Now we can use this test variable in lieu of the cost of goods. And the last item in this list is a little bit unique. It's a parameter for that, I'm going to really quickly jump over to a different data set. You've seen in the news a lot lately, people are doing a lot of forecasting. In this situation, we're modeling population growth over time. So if we take a look, this is our nonlinear platform. What we've got here is some population data. And then we've got a function that we're trying to fit to that population data. So if we take a look at this formula, we're trying to apply an exponential fit to to this particular set of data. You'll notice that now we have some parameters here. So parameters are unique and have local variables that you use within a formula that interact with certain platforms. So for in this case, these two local variables, local parameters, v zero and v one correspond to variables that we're trying to find that we're trying to fit to this particular curve in our data set. So we have starting to use if we hit the Go button, and just do our best fit there, and then we can save these variables back out to our data table. formula here now, jumps nonlinear platform has interacted parameter settings here and up to those parameter setting. Okay, typically on this segment, we take some time for questions. I don't see anything in the q&a then. So I'm going to try and keep the size on time as possible. That's action for today and Julian, back to you.