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.

14 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?

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


    • 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

  5. Kai Reply

    Hi Julie,

    sorry for disturbing, but I’m new with Spotfire and I have a problem, and can’t solve it :(.

    I have the following expression it works fine, but when there ist no value in the pre-month,
    the result is 0 and not the realy changing from 0 to mabe 10 = 10

    Sum([Payment Total LC]) THEN [Value] – First([Value]) OVER (NavigatePeriod([Axis.Columns],”Month”,-1)) as [Payments]

  6. EVA Reply

    //the syntax is the same, you simply wrap the columns in parenthesis. // →means only can put columns in parenthesis, can’t input another expression into the parenthesis.

    say i would like to aggregate the company count per account registration date,
    while the date time is in format of m/d/yyyy hh:mm:ss, e.g. ‘3/30/2021 5:46:42 PM’, i need to remove the hh:mm:ss , then i input express as below (with calculated expression in the parenthesis, not simply columns),
    uniquecount(companyname) OVER(Date([ACCOUNT REGISTRATION DATE]))

    found system return erro ‘can’t find function date on line 1, column 31

    • Julie Sebby Post authorReply

      I wouldn’t do it that way. I would create a calculated column that is just the date and not the date/time. Then you can use uniquecount([company name]) over ([new registration date]).

    • Julie Sebby Post authorReply

      Are you keeping the data external? Date is a function in spotfire, but if you keep the data external, you can’t use all of Spotfire’s expressions. I’m asking that question because really your expression should work, but if the error says it can’t find the function Date, it makes me think your data is external.

  7. Ulysses Mullins Reply


    I am trying to limit data in a table to only read rows containing the marked week as well as the previous 12 weeks. How should I write the limit data expression?

    • Julie Sebby Post authorReply

      You need to use both data limiting with marking and data limiting with expression, and I would employ a multi step / multi visualization process. I would start with one visualization that uses data limiting by expression to show only the previous 12 weeks of data. Then, have the user mark that visualization, and the results show up in another visualization based on the marking of the first visualization.

  8. Brenna Heinrich Reply

    Hi Julie,

    So far, I have not found a way to create a Quarter to Date expressions (for production averages). Is this possible? I have been able to successfully create MTD & YTD, but also need QTD. Thanks!

    • Julie Sebby Post authorReply

      This can be done with axis names using a hierarchy on the x axis. Are you using a hierarchy on your x axis?

      • Brenna Heinrich Reply

        Hi Julie, thank you for your reply! I am not, but will try accomplishing it that way! Thank you!

Leave a Reply

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