- Do you want to learn more about the Spotfire expression language?
- Are you struggling with calculated columns?
Many users come to Spotfire by way of Excel and have learned to write IF, VLOOKUP and INDEX functions with ease. When they get to Spotfire, they find IF statements are just as easy. AND and OR statements are even easier since they require fewer parenthesis, but then the similarities start to vanish. Trying to pinpoint specific cells is difficult without a solid understanding OVER functions and node navigation methods.
This post will discuss the standalone use of OVER. A future post will cover node navigation.
There are several important things to understand about OVER.
- OVER is a keyword. Keywords redirect the expression in some way, as opposed to functions (ex. Avg, Sum), which provide specific aggregation instructions.
- Keywords are used as part of expressions and will be recognized (in blue type) when you type them into the expression dialog 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.
Let’s start simply by seeking to understand OVER when used by itself (without node navigation methods). I like to think of OVER as “for each”. In the data set shown below, the data table contains daily oil and gas production values for a field of wells — one record for each well for each day. With this data set, the expression — sum([OiProdl]) — would return one repeating value that is the sum of all the oil values, as shown in the screenshot.
While this is good, it’s probably not useful, and you need to “slice” the data into a lower level of detail or granularity. The OVER keyword will do this. For example — sum([OilProd]) over [Well Name] — would return the sum of OilProd for each Well Name as shown in the screenshot below.
Furthermore, OVER can be used with multiple columns of data. The expression — sum([OilProd]) over ([Reservoir], [Field]) — would return the sum of oil for each field in a reservoir, which is an important distinction since fields can cross reservoirs. Note, the syntax is the same, you simply wrap the columns in parenthesis.
In my next post, I’ll talk about using OVER in combination with node navigation methods to pinpoint specific records in a data table.
Guest Spotfire blogger residing in Whitefish, MT. Working for SM Energy’s Advanced Analytics and Emerging Technology team!