How to Create Rolling Time Periods in Spotfire

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]

1-Spotfire_Lookback_Period

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.

2-Adjusting_Spotfire_Date_filter

 

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.

  1. Open the custom expression editor within “Limit Data Using an Expression”

3-Edit_Custom_Expression_Spotfire

  1. Type the following into the Expression window:

[Date] > Max(DateAdd(‘mm’, -3, [Date]))

 

4-Custom_Expression_Spotfire

  1. Click OK, then close

5-3_month_lookback_spotfire

 

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:

               Year:           ‘year’ or ‘yy’
               Quarter:      ‘quarter’ or ‘qq’
               Month:        ‘month’ or ‘mm’
               Day:            ‘day’ or ‘dd’
               Week:         ‘week’ or ‘wk’
               Hour:          ‘hour’ or ‘hh’
               Minute:       ‘minute’ or ‘mi’
               Second:      ‘second’ or ‘ss’
               Millisecond: ‘millisecond’ or ‘ms’

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

11 thoughts on “How to Create Rolling Time Periods in Spotfire

  1. Adam M Reply

    Hi Kyle. Very useful post, thank you! One question…when I limit the time range of my bar chart, the bars maintain their original width and the Appearance > Bar Width becomes read only. Is there any way to fix this?

    • Kyle LaMotta Post authorReply

      Adam – try changing the Category axis to a Categorical scale. You can do this by either right clicking on the axis and selecting “Categorical Scale”, or by opening the visualization properties, select “Category Axis” from the options on the left side, click “Settings” in the top right, and then select “Categorical” below Axis mode.

  2. Natalie Reply

    Hi Kyle, will this work for a fiscal year to date? I have a fiscal year offset of -3 in the document properties.

    Thanks!

  3. Vivek Reply

    Thanks a lot Kyle for this logic. I was wondering if we have any logic to get last 7 business days (excluding weekend) ?

  4. Ben Shaiman Reply

    But what if I wanted to look at the last 12 months, and include every day in the month that is a year ago. If the date is the 30th of June, but I want all the data for the last 12 months, this function still only gives me data from the 30th of June the previous year. Is there any work-around for this?

  5. Ben Shaiman Reply

    This only works for giving an absolute, not a relative date. If you are in the middle of a month, and you want the last 3 months, it gives you data from the middle of whatever month was 3 months ago. Is there a way to make it rolling but to cut it off at the 1st of a month, not at an absolute number of days?

  6. Srini Reply

    Hi Kyle

    I need expression to show Last 7 Fridays data in spotfire report kindly help me with expressiom
    Thanks

  7. Christophe ARMAND Reply

    Hi Kyle
    Thanks you for these very good tutorials.
    I have a question.
    I limit graph and table with the expression but when I modify the filter on date they are also modified.
    How can I do to disable the filter when I set the limit expression ?
    Regards

  8. Nancy Riad Reply

    what if I want to limit the data using the current month only regardless of the day, I tried adjusting your custom expression but it didn’t work. Any suggestions?

    • Cetric D Vazquez Reply

      Maybe something like:

      [Date] >= Date(Year(DateTimeNow()),Month(DateTimeNow()),1)

Leave a Reply

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