Using the Intersect Keyword

This is post 5 in my series on learning the Spotfire expression language. It will build on the content of the last few weeks and provide a comprehensive understanding of how over, intersect, and node navigation work together. The intersect keyword is the last piece of the puzzle. Read on to learn how it fits in.

Before I dive into intersect, I want to review what we’ve covered so far.

Review

1. Spotfire calculates down columns of data.
   • Example: Sum([Gas Prod]) will return one repeating value because it is calculating the sum of the entire column.
2. The over keyword groups data in a calculation. When used by itself, over means “for each”. For example, the expression below will calculate the total production for each well.
   • Example: Sum([Gas Prod]) over [Well Name].
3. Over can also group multiple columns. Simply separate the columns with a comma and put them in parenthesis. The example below will sum up the gas prod for each county and production date.
   • Example: Sum([Gas Prod]) over ([County], [Production Date])
4. Over can be used by itself or it can be used with node navigation.
   • Example of over used by itself: Sum([Gas Prod]) over [Well Name]
   • Example of over used with node navigation: Sum(Gas Prod) over Previous([Production Date])). This will return the sum of gas prod for the previous production date.
5. Node navigation is a way of directing calculations to specific values or groups of values in a calculation.
6. Node navigation must be used with over. It cannot be used alone.
7. When writing expressions on the axis of a visualization, users must often place a hierarchy on an axis rather than a “just” a column of data.

For more details on any of the bullets above, check out the links at the bottom of this post. Now, let’s talk about the intersect keyword.

The Intersect Keyword

Intersect is used in conjuction with over and node navigation. It can’t be used on its own. It returns the intersected rows from the columns specified. If that explanation isn’t clear, let’s look at an example.

In the review, I used the expression — Sum(Gas Prod) over Previous([Production Date])) — to return the sum of gas prod for the previous production date. But, what if you want to return the sum of gas prod for the previous date for each well? Now, you need the intersect keyword. Here’s the expression to make it happen.

Sum([Gas Prod]) over Intersect([Well Name], Previous([Production Date]))

When you want to group by something AND navigate to a specific row, you must use the intersect keyword. Here is a summary of the progression.

• Adds up all gas production = Sum([Gas Prod])
• Sums gas production for each well = Sum([Gas Prod]) over [Well Name]
• Sums gas production for the previous date = Sum([Gas Prod]) over Previous([Production Date])
• Adds gas production for each well for the previous production date = Sum([Gas Prod]) over Intersect([Well Name], Previous([Production Date]))

That may seem short and simple, but that’s all there is to it!

Wrap Up

Now that you understand over, intersect, and node navigation, you should be able to slice and dice calculations any way you want.

In the post entitled Using Node Navigation in Spotfire, I explained the concept of a node and what node navigation does. But, I only showed a list of all the different node navigation methods. Next week, I’ll elaborate on what each one of them does and when to use them in calculated columns versus on the axis of a visualization.

Spotfire Version

Content created with Spotfire 10.2.

Build Your Knowledge With These Posts

• Learning the Spotfire Expression Language
• Using the Spotfire Over Keyword
• Using Node Navigation in Spotfire