Last week, I kicked off a series on learning how to use the Spotfire expression language. The first post explained the 2 different ways to create calculations in Spotfire. This week, I’m going to talk about the over keyword and how to use it. Without the over keyword, it’s impossible to really get into the Spotfire expression language. It’s also a good starting point for learning node navigation. Read on to learn more.
Why do we need the over keyword?
Most users come to Spotfire from Excel, and they expect calculations to work the same way as Excel….but they don’t. In Excel, you can point a calculation to a single cell like $A$2. In Spotfire, all calculations are performed down a column of data. The expression Sum([Gas]) will return one repeating value. In order to create a more precise calculation, you must use the over keyword at a minimum. The intersect keyword and node navigation methods also help point Spotfire to specific cells. For now, let’s focus on over. And before we get too far, I want to help you understand what a keyword is in Spotfire.
Keywords in General
Sometimes, when you are trying to solve a problem with Google, half the battle is getting the terminology right. Figuring out how to use over is easier when you know that over is a keyword. Here’s what’s important about over and keywords.
- Keywords redirect the expression in some way, as opposed to functions (ex. Avg, Sum), which provide specific aggregation instructions.
- Keywords appear in blue type when you type them into the expression dialog (shown below) but they are not searchable in the functions dialog.
- OVER is also a category of functions, under which all node navigation methods are listed. When you select one of the OVER functions, OVER will be inserted into the expression as well as the node navigation method selected.
- OVER can be used without node navigation methods, but node navigation methods will always be used with OVER.
For a full list of Spotfire keywords, check out this TIBCO document.
What Does Over Do?
Over groups data in a calculation. To better understand, read the expression like a sentence, and substitute “for each” in place of “over”. Try it with the expression below.
Sum([Gas Prod]) over ([Well Name])
Sum gas prod for each well name. That was easy!
Here’s the same expression with data. The calculation is the last column. As you can see, the calculated value repeats the same number for each well.
Using Over with Multiple Columns
You can also use over with multiple columns or multiple groupings. Just separate them with a comma as shown here.
Sum([Vol Equiv Prod Boe]) over ([Well Completion Name],[c.First of month])
In this case we are summing BOE for each Well Completion Name and each Month. Add as many column as you need to get the desired grouping.
Now that you know how to use over, next week we’ll tackle node navigation.
Content created with Spotfire 10.2.
Pingback: Using the Then Keyword » The Analytics Corner
Pingback: Node Navigation - All, Next, Previous, AllNext & AllPrevious » The Analytics Corner
Pingback: Spotfire Errors Querying Two MS Access Tables » The Analytics Corner
Pingback: Node Navigation - Parent & ParallelPeriod » The Analytics Corner
Pingback: Master NavigatePeriod Node Navigation » The Analytics Corner
Pingback: Using Node Navigation in Spotfire » The Analytics Corner
Is there any restriction in using over keyword in Expression for Calculated Value(The one available in text area)?
Not that I know of. Are you running into a particular problem?