Spotfire OVER function examples in Custom Expressions of Visualizations

This blog post demonstrates several examples of Custom Expressions using the OVER function. In a couple of previous blog posts I’ve focused on using the OVER function in calculated columns, so to mix it up this time we’ll be looking at OVER functions within a visualization.

1-Spotfire_Examples_using_the_OVER_Function

If you have any ideas for other custom expressions, please comment below!

[ninja-popup ID=1649] Click here to download the Spotfire file shown in these examples!! [/ninja-popup]

If you haven’t already, be sure to reference the other posts with OVER examples:

A few notes on these expressions:

  • Make sure the X axis is set to Categorical Scale. Anytime the OVER function is used in a visualization custom expression, the axis that the equation is referring to must be Categorical (not Continuous). If it’s Continuous, you’ll get the error message “Could not find Axis: ‘X’. There are 3 ways to set your Date axis to a categorical scale:

Option 1: Use one of the built in Date Hierarchies (or a hierarchy column). For example, Date: Year >> Month as in the screenshot below:

2a-Spotfire_set_categorical_scale

 

Option 2: Right click on the axis and select “Categorical Scale”

2b-Spotfire_set_categorical_scale

 

Option 3: From the Visualization Properties, select X Axis (Or Category axis if using a Bar Chart), Settings, then select Categorical under Axis Mode

2c-Spotfire_set_categorical_scale

  • Some of these expressions (such as those using “PreviousPeriods”) assume that the raw data is already aggregated at the monthly level – if your data is on a different time scale, just be aware you might have to tweak these expressions a bit
  • Anything wrapped in square brackets [ ] refers to a column name in Spotfire. If copying and pasting these expressions, just replace the column name in the brackets with the column from your data.
  • As all datasets are different, you may need tweak an expression to work with your data. If you run into any issues, feel free to leave a specific question in the comment section below and I’ll try to recommend an expression for your data.

 

OVER Function examples in Custom Expressions

Example 1 – Average of all values on the chart

Avg([OIL]) OVER All([Axis.X])

3-OVER_to_average_all_values_in_a_chart

 

Example 2 – Overall average of the current Year

Set Date axis set to Year >> Month. There are 2 levels in the Date Hierarchy, Year and Month. Year is the Parent of Month, so the average yearly production would be:

Avg([OIL]) OVER Parent([Axis.X])

4-Spotfire_OVER_to_calculate_the_average_of_a_time_period

 

Example 3 – Average of all lines on the chart – Option 1

Select a column from your data to Line By, and then Color By: (Column Names). This visualization requires 2 series on the Y Axis:

Sum([OIL])

Avg([OIL]) OVER All([Axis.Line])

5-Spotfire_OVER_to_average_all_lines_on_a_chart

 

Example 4 – Average of all lines on the chart – Option 2

This is similar to Example 3, but allows you to color each line differently. Select 2 columns on the Color By axis, where one of them is: (Column Names). The only problem with this chart is that you end up with duplicate average lines. It doesn’t affect the graph, just the colors in the legend (You’ll have to set multiple series as the same color).

Avg([OIL]) OVER All([Axis.Color])

6-Spotfire_OVER_to_average_all_colored_lines_on_a_chart

 

Example 5 – Cumulative Sum, or Running Total

Sum([OIL]) OVER AllPrevious([Axis.X])

7-Spotfire_OVER_to_calculate_cumulative_sum

 

Example 6 – Cumulative Sum for Current Year, or Year to Date Total

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

8-Spotfire_OVER_to_calculate_YTD_Total

 

Example 7 – Rolling Average, or Moving Average

3 Month moving average (average of the last 3 time periods). Just adjust the number in LastPeriods to change the number of periods you’re averaging:

Avg([OIL]) OVER LastPeriods(3, [Axis.X])

9-Spotfire_OVER_to_calculate_moving_average

 

Example 8 – Difference between Current Month and the Previous Month

Set X Axis to Date: Year >> Month. Previous is referring to 1 prior period on the X Axis, so if the current month is June, the expression below would subtract May from June. This also works for other date hierarchies, such as Year >> Quarter.

Sum([OIL]) - Sum([OIL]) OVER Previous([Axis.X])

10-Spotfire_OVER_to_calculate_difference_from_previous_month

 

Example 9 – Difference between Current Month and the same Month of the Previous Year

Set X Axis to Date: Year >> Month. ParallelPeriod is referring to 1 prior period on the Parent node, which would be Year since Year is the Parent of Month. This also works for other date hierarchies, such as Year >> Quarter.

Sum([OIL]) - Sum([OIL]) OVER ParallelPeriod([Axis.X])

11-Spotfire_OVER_to_calculate_difference_of_same_month_in_previous_year

 

Example 10 – Rate of Change, or Percent Change from Previous Month

To calculate percent change by hand, we’d use the following equation: % Change = (y2 – y1) / y1. Rearranging the equation we get: % Change = y2 / y1 – 1. Substituting y1 and y2 for Spotfire functions, the expression becomes:

(Sum([OIL]) / Sum([OIL]) OVER Previous([Axis.X])) - 1

12-Spotfire_OVER_to_calculate_percent_difference

 

[ninja-popup ID=1649] Don’t forget to download the Spotfire file shown in these examples!! [/ninja-popup]

Questions / Comments
Hopefully these examples have helped you understand how to use the OVER function within visualization custom expressions. If you have any questions or comments, or if there’s something you’d like featured on this blog, feel free to email me at kyle@datafuel.co or leave a comment below.

12 thoughts on “Spotfire OVER function examples in Custom Expressions of Visualizations

  1. Matt Reply

    Awesome. Thank you very much for your thorough and detailed post! It is very helpful.

    I’m constructing a moving average filter, and am curious if there’s a way to have the moving averaged range centered on the non-filtered range (i.e., average from n-25:n+25, instead of n-50:n). I can accomplish the latter per your Example #8, but am not sure if the former is possible.

    Thanks again!

    • Kyle LaMotta Post authorReply

      Matt,

      Glad to hear this was helpful, thanks for the feedback!

      Give this a shot. For a given time period on the X axis, it will subtract the average of 25 periods after that point from the average of 25 periods prior to that point.

      Avg([OIL]) OVER (NavigatePeriod([Axis.X],0,-25)) - Avg([OIL]) OVER (NavigatePeriod([Axis.X],0,25))

      Let me know how that goes.

      -Kyle

  2. JD Reply

    I have a dataset that does not have a good date column. I’m trying to use 1,2,3……x on X-axis for number of producing months, but I have no reference to date. I’ve tried using an “IF” statement to identify months > 0 production with a “1”. I then try to get a running sum but all it does is give me the TOTAL (ie 32 months = 1+1+1+1……=32) instead of oilprod month 1 = 1, oilprod month 10=10, etc., etc…… I can’t figure it out.

    • Kyle LaMotta Post authorReply

      JD,
      Try adding a calculated column for the date – There is a Date function in Spotfire that will convert columns and integers into a date column, which can then be used to look at trends over time. The syntax is: Date(Year, Month, DayofMonth) So if you have 2 columns, [Month] and [Year], but not a column for the day of the month, you can insert a calculated columns with the expression: Date([Year], [Month], 1), where 1 is an integer for the first day of the month.

  3. vanice souza Reply

    This post is very interesting. I need a help.

    In the data table contains the following fields:

    * Brands
    Year
    * State
    * Sales classification (High, Medium or Low)
    * Total area purchased

    I have to make a chart that contains:

    * General average line of the sum of all the marks, being its variation according to the chosen filter.

    And another line containing:

    * Among all tags, for example when you choose two of them in the filter, these two lines appear in the graph compared to the line above.

    In other words, this would be 3 lines.

    I saw his examples and I got very close, but I could not make the first general average line of the sum.

    Can you help me?

    I was able to do it in excel but in Spotfire I can not.

  4. Eugene Reply

    Kyle,

    Thanks for the post! I’m late to the game. If I’ve created columns with daily oil volumes per lateral foot, how would that change the formula if I’m trying to show the average daily oil per ft if colored by first production year? Also, how would the formula for the Cum plot be changed?

  5. Justin Biddle Reply

    is there a way to find avg line from selected lines on a continues x axis?

  6. r_b Reply

    Hello Kyle
    I want to calculate the difference between one period and the one three and six months ago. For the difference to last month I have:
    Sum([Sales]) over (Intersect([product id],Previous([Date])))

    Is there something to adjust the formula to let Spotfire know to use the Date which is “three months back”?

    Thanks!

Leave a Reply

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