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.
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:
- OVER Functions in Calculated Columns
- OVER Functions in Calculated Columns using the Intersect Function
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:
Option 2: Right click on the axis and select “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
- 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])
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])
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:
Avg([OIL]) OVER All([Axis.Line])
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])
Example 5 – Cumulative Sum, or Running Total
Sum([OIL]) OVER AllPrevious([Axis.X])
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))
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])
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])
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])
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
[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 firstname.lastname@example.org or leave a comment below.