Search code examples
ironpythonspotfire

How can I automatically update a filter when my analysis is opened?

I have a list of dates in a column:

2015-01-01
2015-02-01
2015-03-01
2015-04-01
2015-05-01

We now have 1st February, so I save the dashboard with filtering set to keep only rows where date is between 1st February and 1st April. Next month, I will need to update the filter to between 1st March and 1st May (increase the bottom and top ranges of the date filter by one month).

Is it possible that such filtering happens automatically without manually adjusting the filter?

Solution

  • there are a couple of options:

    1. you can create a calculated column that returns a boolean TRUE or FALSE if the date falls between a given range using DateDiff(). then, simply set the filter to this column as TRUE, and only that data will be displayed. this is the simplest method!

    2. depending on your desired result, you can limit the data that enters the analysis in the first place. you can do this if your data source is an information link or DB connection, but will probably not be able to do it if you're working off of Excel or CSV data. simply attach a WHERE clause to the query in the Information Designer that limits the statement to select the date range you need.

    3. you can adjust a filter via IronPython scripts, however Spotfire has no built-in mechanism for running a script when the document is opened*. since you're working with a Range filter you can use some code like the below.

    I'll leave the mysteries of Python date math up to you, but this should get you started. NOTE that you will need to attach this code to a button or some other user action; again, it's not automatic.

    from Spotfire.Dxp.Application.Filters import ValueRange, RangeFilter
from datetime import datetime, timedelta

#these are both CaSeSeNsItIvE
data_table_name = "Compliance Items"
column_name = "EventTimestamp"
days_ahead = 60

start = datetime.now().strftime('%Y-%m-%d') # today's date
end = (datetime.now() + timedelta(days = days_ahead)).strftime('%Y-%m-%d') # days_ahead in the future

filter_range = ValueRange(start, end) # create a ValueRange to set the filter

dt = Document.Data.Tables[data_table_name] # get data table reference by name

# get filter reference by column name
filter = Document.FilteringSchemes.DefaultFilteringSchemeReference[dt][column_name].As[RangeFilter]()

filter.ValueRange = filter_range

    * there is a way to combine Javascript and IronPython scripts to automatically execute a script when the document is opened, but I think that for this use case it's overkill.