- Do you want to learn more about the Spotfire expression language?
- Are you struggling with calculations?
- Do the expression shortcut calculations look completely foreign?
In my last Data Shop talk post, we discussed the use of the OVER function by itself. This post will cover OVER when used with node navigation methods.
I dipped into the Help section a starting point but just found it too confusing, so I’m going with my own simple explanations.
To recap, in the previous post, I described OVER as “for each”. The expression — sum([OIL]) over [Well Name] — will return the sum of all oil for each well name (note: there is no consideration for time in this expression, so it will add up all oil for all wells for all records in the data set).
The next logical question is what is a node? Quite simply, a node is a column of data, that will specify how data should be sliced and diced. Okay, then what is a node navigation method? A node navigation method is an function specifying how to navigate within the data table relative to the “current” record.
For example, if you have a production data set and you want to know what yesterday’s oil production was for each well, the nodes you will reference will be a well identifier column and a date column, such as Well Name (or API) and Production Date. The nodes will be used in an expression with the Previous node navigation method. When the expression looks at each record (the current record), it returns the previous day’s oil production for each well. Why don’t I just show you an example? Well, there is one more piece to the puzzle — the Intersect function.
When you use OVER and node navigation methods together, you may also need to use the Intersect function to specify the level of granularity the calculation should take place at.
For example, the expression you would use to calculate yesterday’s oil production for each well is this: Sum([OIL]) over (Intersect([Well Name], Previous([Production Date])))
The intersect function is specifying that the calculation should be performed for each Well Name. The Previous node navigation method specifies the expression should look at the previous Production Date relative to the current Production Date as shown in the screenshot below.
- OVER can be used alone (as we learned in the last post)
- Node navigation methods will always be used with OVER
- There are 14 different node navigation methods. Not all of them are meant to be used in inserted calculated columns. Some are specifically meant for writing expressions directly on the y axis of a visualization, which will be the subject of an upcoming post.
- When you use OVER and node navigation methods together, you may also need to use the Intersect function to specify the level of granularity.
The node navigation methods can be found in the OVER list of functions, along with the Intersect function. When you insert them, OVER is inserted is well.