Often times it’s appropriate to look a recent trend or the performance of something, without looking at the entire history. For example, an oil well might have been producing for many years, but from a diagnostic perspective you may only care about what it’s done in the last 3 months. Or 6 months. Or 7 days, or whatever time period you come up with.
This can be called a “rolling time frame” or a “look-back period”.
[ninja-popup ID=1262] Download the Spotfire example shown in the Screenshots [/ninja-popup]
Downside of Filtering
One way to zoom in on this time period is by simply adjusting the filter of your Date column. This is fine for a one time analysis, or ad-hoc investigations, but you may have a report that always needs to show the last 3 months (or whatever time period). Fiddling with the filter suddenly become much more cumbersome, repetitive, and annoying.
The solution? Write a custom expression to do the filtering for you.
Limit Data with an Expression
Fortunately there’s a very simple expression you can write in Spotfire to do this that will save you tons of time and frustration (woo!). Instead of using a filter, the data will be limited with a custom expression.
If you’ve never used the Limit Data with Expression feature or need a refresher, check out my last blog post for a quick overview: http://www.datafuel.co/how-to-limit-data-using-an-expression-in-spotfire/
Benefits of limiting data with an expression:
- Only the visualization with the limiting expression will be changed – all other visualizations will not be affected
- Set it and forget it. Unlike a filter, as new data comes in, it will always be looking at the time period you specify (e.g. last 3 months)
- Hard to accidentally change. Unlike a filter, which can be easily reset, or a bookmark that can be overwritten, the data limiting expression is hard to accidentally change or delete
How to Write the Expression
In the screenshot above, I’ve setup a 3-month look-back, which shows production values for the most 3 recent months only (my sample dataset ends in Oct 2015, so let’s assume that today is Nov 1, 2015).
If you read the last blog post, the above example is very similar, except that I’ve changed the data limiting expression for the line chart and cross table.
- Open the custom expression editor within “Limit Data Using an Expression”
- Type the following into the Expression window:
[Date] > Max(DateAdd(‘mm’, -3, [Date]))
- Click OK, then close
To customize this expression for a different time period, modify the number of months, or the date part.
Breaking down this Expression
The way I interpret this expression is: “Only return data from the most recent 3 months of the Date column”. Here are the different pieces:
[Date] – The date column from the data table
Max() – Takes the biggest values from the DateAdd expression, ensuring that only the biggest 3 values for Date, and therefore the most recent, are returned
DateAdd – Adds an interval (in this case it subtracts, because of the negative) to a date column. ‘mm’ is an optional argument describing the date part (months). Available arguments for date part are listed below:
Another common example would be to look at data from the last week, or the last 7 days. This expression would be: [Date] > Max(DateAdd(‘dd’, -7, [Date]))
Questions / Comments
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 email@example.com or leave a comment below.