How to use the OVER Function in Calculated Columns in TIBCO Spotfire

[ninja-popup ID=912] Click Here to download the Spotfire template shown in the video[/ninja-popup]

The OVER function in Spotfire can be very useful when writing custom expressions for calculated columns in Spotfire. It’s used to divide a column into different groups, so that you can evaluate a calculation for each group, instead of the entire column. Unfortunately, examples with OVER expressions in calculated columns are not very¬†well documented in the help files. There are several examples for using it in visualization axis selectors, but not in calculated columns. If you’re familiar with writing SQL statements, the OVER function is very similar to GROUP BY.

In the simple example below, we have a table of data with 2 distinct groups. Taking the SUM of the Value column will add all of the Values in that column. However, using the OVER function will divide the data into 2 groups, Group 1 & Group 2, and then SUM the rows in the Value column for each one of those groups.

Post 5 - Data Table Screenshot

In this video, I demonstrate how to use the OVER function in a calculated column in Spotfire for 3 different examples:

  1. Find the peak oil production for each well (the maximum value that a well has ever produced in a month)
  2. Find the annual peak oil production for each well
  3. Calculate the cumulative production for each well

Spotfire functions demonstrated:

  • Max
  • Sum
  • OVER
  • AllPrevious
  • Intersect

If the embedded player above isn’t working,¬†this video is also on YouTube: https://youtu.be/mudSAbHTtks

If you have any questions or comments, feel free to email me at kyle@datafuel.co, or leave a comment below.

Next Post: Learn how to use CASE statements to simplify your IF statements. In the next post, I’ll pick up with this same dataset, but instead look at CASE statements in Calculated Columns.

[ninja-popup ID=912] Click Here to download the Spotfire template shown in the video[/ninja-popup]

9 thoughts on “How to use the OVER Function in Calculated Columns in TIBCO Spotfire

  1. Anonymous Reply

    Can you also show examples on how to use the over, intersect etc functions in a cross-table?

  2. Om Prakash Reply

    Awesome Video to understand the over function and allprevious concept.
    Thanks a lots.
    We similarly expect the same conceptual video for spotfire development.

    • Kyle LaMotta Post authorReply

      Hi Om – Thank you for the note – I’m really glad this was helpful! Don’t hesitate to let me know if there’s other examples you’d like to see.

  3. Tammy Vanecek Reply

    Can you explain the Intersect function in a little more detail? Specifically when you say intersecting two columns that are in two different hierarchies.

    For example: I have an OVER (Intersect) function and it is pulling average oil per well for the last 30 days. Here is my expression: Avg([OIL]) OVER (Intersect([DRI_LEASE],LastPeriods(30,[D_DATE])))

    I am having trouble determining why the Intersect function was used here. Which pieces of data are on different hierarchies?

    • Kyle LaMotta Post authorReply

      Hi Tammy ,

      In your example, using multiple columns in the OVER Statement with the LastPeriods function requires you to also use the Intersect function. The “different hierarchies” wording is confusing. Instead of trying to interpret what Spotfire means by that, I think it’s easier to just know when to use it, which I’ve tried to outline in the blog post published today:

      http://www.datafuel.co/more-over-function-examples-multiple-columns-intersect-function-and-lebron-james/

      There you will find many more examples of the OVER And Intersect functions, along with a list of all the functions requiring Intersect.

      Let me know if that answers your question or if you’d like another example. Thanks!

  4. Pingback: More OVER Function Examples: Multiple columns, Intersect function, and LeBron James | Datafuel

  5. Natalia Reply

    This video was exactly what I needed for a project I was doing at work. It helped me alot, thank you for posting this!

    • Kyle LaMotta Post authorReply

      Natalia – thank you for letting me know! So happy to help. Let me know if there’s anything else you need help with.

      Kyle

  6. Anonymous Reply

    Would it be possible to somehow RANK the Sum([Value]) OVER [Group]?

    So, in this example, assuming only Group 1 and 2 are in the data set…Group 1 would then get a ranking of 2 (Sum([Value]) OVER[Group] = 5 ) and Group 2 a ranking of 1 (Sum([Value]) OVER[Group] = 10)

Leave a Reply

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