Business Intelligence Tools

Spotfire Expressions — over statements

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

  1. OVER is a keyword.  Keywords redirect the expression in some way, as opposed to functions (ex. Avg, Sum), which provide specific aggregation instructions.  
  2. 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.
  3. 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.
  4. 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.

8-15-2016 3-29-48 PM

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.

8-15-2016 3-30-05 PM

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.  

8-15-2016 3-30-19 PM

In my next post, I’ll talk about using OVER in combination with node navigation methods to pinpoint specific records in a data table.

5 thoughts on “Spotfire Expressions — over statements

  1. Pingback: Axis Names for Spotfire Expressions

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

  3. T. Ung Reply

    Hi Julie,

    Would you happen to know how to get the Cumulative data from all previous periods excluding the First period or value?

    E.g. Example 6 from Kyle’s post shows how to get the cumulative per year, how would we exclude the first year or first month?

    https://www.datafuel.co/2016/06/28/spotfire-over-function-examples-within-custom-expressions-of-visualizations/#comment-767

    Sum([OIL]) OVER Intersect(AllPrevious([Axis.X]), NavigatePeriod([Axis.X], “Year”, 0, 0))

    Thanks!

    • Julie Sebby Post authorReply

      If you want to exclude a period, the easiest thing I know how to do is create a different column to reference in the expression you gave that excludes that period. then you don’t have to worry about trying to incorporate logic into an already complicated expression. Also, the expression language doesn’t really allow for exclusions or exceptions.

  4. Pingback: Creating a Snapshot of Yesterday with Expressions » The Analytics Corner

Leave a Reply

Your email address will not be published. Required fields are marked *