turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Formula to use previous value if not missing, othe...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 20, 2016 10:18 AM
(2171 views)

I'm trying to write a formula in Col3 that adds the current value of Col1 with the lagged value of Col2 but I'm running into a pickle trying to figure out how to carry forward the last observation from Col2 when missing data is encountered and to use this value at the first instance of non-missing data. That is,

If( !IsMissing( Col1)

Col1 + Lag(Col2,1);

create variable that contains current Col2 value;

//Else

Col1 + previously stored value from Col2;

);

I would appreciate any suggestions on how best to accomplish this. Thanks.

Row# | Col1 | Col2 | Col3 |
---|---|---|---|

1 | 2 | 1 | |

2 | 1 | 3 | 2 |

3 | 1 | 5 | 4 |

4 | 2 | 7 | 7 |

5 | . | . | |

6 | . | . | |

7 | . | . | |

8 | 2 | 8 | 9 |

9 | 2 | 9 | 10 |

10 | 1 | 11 | 10 |

11 | . | . | |

13 | . | . | |

14 | 2 | 12 | 13 |

15 | 2 | 13 | 14 |

16 | 1 | 14 | 14 |

17 | 2 | 15 | 16 |

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Solution

I think this formula will give you what you're after:

If(Is Missing(:Column 1), :Column 1, Summation(i = 1, Row(), :Column 1*))*

Hope this helps.

-Jerry

7 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 20, 2016 12:26 PM
(1901 views)

Here is one version of the formula that works:

If( Row() == 1, lagval = . );

x = Sum( :col 1, lagval );

If( Is Missing( col 2 ) == 0,

lagval = :col 2

);

x;

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 20, 2016 12:31 PM
(1901 views)

I think there's an error in your description that we'll have to clear up before we can give you a precise solution. Your conditional says if that if Col1 is NOT MISSING then add it to the lagged value of Col2, and if Col1 IS MISSING then add that to a previously stored value of Col2.

You can't add a missing Col1 to anything and get anything other than missing.

Having said that, I would build this table out one column at a time. Specifically, I would create a "non-missing Col2" column which had no missing values using the formula:

This formula sets "non-missing Col2" to Col2 when Col2 isn't missing and sets it to the Lag of "non-missing Col2" when Col2 is missing.

You can then use a Lag(non-missing Col2, 1) in your final formula.

I hope that helps.

-Jeff

-Jeff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 20, 2016 1:21 PM
(1901 views)

Thanks Jim and Jeff,

I think I made the issue more complicated than it needs to be in my description and table layout and can see how that may have led you to your suggestions.

While I understand your suggestion Jeff about creating a new column that holds the non-missing data I'm hoping to accomplish my work without creating new columns since I'll be using this formula for many different columns. Let me try to be more clear about what I'm trying to accomplish by way of the table below.

I'm trying to create a running sum of Column 1 in Column 2. Without creating any additional columns, I was hoping to create a variable that contains the current running sum value in Column 2 and adds that to the value of Column 1.

My issue is not knowing the best way to create and use a variable that contains the present value for Column 2 so that this value can be "carried" across rows of missing Column 1 data. Column "Result I'm Looking For" shows what I would like the formula in Column 2 to produce. Hopefully this description is a bit more clear about what I'm looking to accomplish.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I think this formula will give you what you're after:

If(Is Missing(:Column 1), :Column 1, Summation(i = 1, Row(), :Column 1*))*

Hope this helps.

-Jerry

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 26, 2016 5:24 AM
(1901 views)

I run into this problem all of the time and use the solution give by Jerry. I analyze process measurements (temperature, pressure, flow, etc.) that are retrieved from a compressed-data-historian. Every few thousand points, there can be a missing value.

Example below calculates if reactor is running or not based on 3 point moving average of catalyst flow, F.Cat >= 11 (anonymized yes, you get the reference...). If F.Cat is missing, calc uses previous row's value of "Running?".

If(Row() < 3, 0, Is Missing(:F.Cat), Lag(:Name("Running?"), 1), (Lag(:F.Cat, 2) + Lag(:F.Cat, 1) + :F.Cat) / 3 >= 11, 1, 0)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 20, 2016 2:03 PM
(1901 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 25, 2016 11:00 AM
(1901 views)

Thanks everyone for your comments and help.

I think Jerry's suggestion works the best for me and didn't require the use of a local variable which is what I was wanting to use.