- 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.

To summarize:

- 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.

Finally got a valid custom expression using Intersect — thanks! Issues with adding a “then” clause. Trying to average values [VALUE*] over one category [CatA], then sum those averages over another category (Axis Color) — I want my values to change with my filtering, hence using a Custom Expression. I end up with a bar chart that averages by Axis Color and filtering within [CatA], but doesn’t perform the Sum. Your help is greatly appreciated!

avg([VALUE*]) over (Intersect([Axis.X],[CatA])) then sum([Value]) over (Intersect([Axis.X],[Axis.Color]))

I learned how to use keywords like THEN by looking at Expression Shortcuts like Difference and Difference %. Those aggregations / expression shortcuts demonstrate the use of keywords. I looked at Difference % and a few others, and one thing I observed is that none of them use aggregations like Sum, which would add multiple rows together. Instead, they use First, which would only pull one record. I’m not sure that you can use Sum in this use case. I did notice that if I changed First to Sum, the result did not change in Difference %. What you might try instead is to nest the Sum and Average rather than using then. I’m not sure if that will work on the axis, something like Sum (avg([VALUE*]) over (Intersect([Axis.X],[CatA])) ) over (Intersect([Axis.X],[Axis.Color]))

Thanks for the reply! Unfortunately your version of the expression just sums all the contributing values — no sums of averages.

This might be complicated by the fact that I am not using my CatA on an axis — only for a calculation. This person’s example is exactly what I am working through (https://community.tibco.com/questions/sum-averages-spotfire). In my case, I am trying to average the Amount per Item per Category, then only display the sum of the averages per Category. The final result would display sum bars, colored by category, and arrayed by Group on the x-axis.

I have to tried to work from a calculated column of averages, but since the average value is deposited into each source row, I am still having the issue of summing the same value multiple times. I essentially want to collapse these rows into a summary table, then plot the summary table….I might try to pull a single value for each Group/Cateogory/Item, but that seems like a silly work around.