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 a____verage 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:

`Sum([OIL])`

`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 kyle@datafuel.co or leave a comment below.

ningthouAwesome post – thanks a lot!!!

Kyle LaMottaPost authorNo problem, happy to help!

RamGood post

Kyle LaMottaPost authorThanks Ram, glad it you enjoyed it!

MattAwesome. 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 LaMottaPost authorMatt,

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

JDI 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 LaMottaPost authorJD,

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.vanice souzaThis 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.

EugeneKyle,

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?

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

r_bHello 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!