Subtract most Recent Non-Zero Value from next Most Recent Non-Zero Value

[briankuhlmann]

In a perfect scenario, I have a value in both column 12 and column 11. I would need simple [12]-[11] formula.

The issue is, sometimes there isnt a value in 11. For instance if I have a value in [12] and 0.00 in [11],[10],[9], then a non 0 value in [8], I would need to do [12]-[8]

Is there a formula I could use to detect the next non 0 value in columns? Also if [12] is a 0.00 I would require no actions

[DataNath · Accepted solution]

Hey @briankuhlmann, without writing a massive Formula out that is full of hard-coding, here's my initial thoughts on this. I've just drafted up a quick example in the absence of sample data, using fields 1-5.

1) Transpose the data, grouping on [Code] - in your data this is the [SUPPLIER_ITEM_NUMBER]

2) Filter out 0 values

3) Sample for the last 2 records for each [Code]/[SUPPLIER_ITEM_NUMBER], taking the last 2 records from each to get your X - Y values

4) Use a Multi-Row Formula to take the 2nd from last non-0 value from the last non-0 value

5) The Multi-Row Formula will produce one null record and one actual result so we just use a Summarize to take the Maximum of these for each [Code]/[SUPPLIER_ITEM_NUMBER]

6) Join back to the main data set on [Code]/[SUPPLIER_ITEM_NUMBER]

7) Use one final Formula to say that if the last field ([5] in my example) is 0 then just keep it as 0, otherwise use the result we have calculated

Hope this helps - do let us know if not!

[PhilipMannering]

Here's how I'd do it. Similar approach to @DataNath 

[briankuhlmann]

Thank you everyone, worked like a charm.