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

Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!

Pingback: Axis Names for Spotfire Expressions

KFinally 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]))

Julie Schellberg of Big Mountain Analytics, LLCPost authorI 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]))

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

Pingback: Cumulative Sum on Bar Chart is Wrong • The Analytics Corner

DustinHi Julie, I have been looking into something similar but with a KPI chart. I have a table that can have multiple occurances of an AssetID with various time stamps, and each with an HoursEarned value.

I’ve been trying to Sum the Hours earned value for the entire table, but only for the first occurance of each Asset ID.

In short, my table would appear as AssetID – Time – Hours earned.

and my result would be something like Sum(HoursEarned) Over First(Time) Over AssetID.

Julie SebbyPost authorI think I understand what you want to do. There might be more than one option. You can write an if statement that only populates your HoursEarned column for the first occurrence of each AssetID and then sum that column. OR, you might try this…

Sum(First([HoursEarned]) over [AssetID])

I don’t know if that will work. I don’t have a data set to try it out on, but it’s worth a shot.

Also, if you are still learning about node navigation and spotfire expressions, check out this post. It’s part of my Learning the Spotfire Expression Langauge Series.

https://www.bigmountainanalytics.com/what-are-spotfire-axis-names/

Julie SebbyPost authorDustin, I also need to mention that the First aggregation method may not get you what you want. First and Last are based on the order in which the data comes into Spotfire. It is NOT based on any sorting you have applied to the table.