Date Range Expressions in Spotfire

Introduction

Attached find an example that limits the data of visualization via expressions through document properties. There is another example that changes the date range filter programmatically when a document property changes. 

Using Custom Expressions

Today			[Date]=Date(DateTimeNow()) 
Last 30 Days		[Date] >= dateadd('dd',-30,DateTimeNow()) 
This Week		Week([Date]) = week(DateTimeNow()) and Year([Date]) = year(DateTimeNow()) 
This Month		Month([Date]) = month(DateTimeNow()) and Year([Date]) = year(DateTimeNow()) 
Year to Date		Year([Date]) = Year(DateTimeNow()) 
Last Month		Month([Date]) = Month(dateadd('mm',-1,DateTimeNow())) and Year([Date]) = Year(dateadd('mm',-1,DateTimeNow())) 
Last 12 Months		[Date] >= dateadd('mm',-12,DateTimeNow()) 
Q1		Quarter([Date]) = 1 and Year([Date]) = Year(DateTimeNow()) 
Q2		Quarter([Date]) = 2 and Year([Date]) = Year(DateTimeNow()) 
Q3		Quarter([Date]) = 3 and Year([Date]) = Year(DateTimeNow()) 
Q4		Quarter([Date]) = 4 and Year([Date]) = Year(DateTimeNow()) 
ALL		1=1 

Using IronPython

Using IronPython script to change the Date Range Filter programmatically is a bit trickier since it has to find the actual values to filter, but the logic is all there:

from Spotfire.Dxp.Application.Filters import RangeFilter, ValueRange 
from System import DateTime  

dateFilterName="Date" 
#option="Last 12 Months"  

d1,d2=DateTime.Now,DateTime.Now  

if (option=="Today"): 	
	d1 = d2# + d.date 
if (option=="Last 30 Days"): 	
	d1 = d2.AddDays(-30); 
if (option=="This Week"): 	
	d1 = d2.AddDays(-7 if int(DateTime.Now.Date.DayOfWeek)==0 else -int(DateTime.Now.Date.DayOfWeek)); 
if (option=="This Month"): 	
	d1 = DateTime(d2.Year, d2.Month, 1).Date; 
if (option=="Year to Date"): 	
	d1 = DateTime(d2.Year, 1, 1).Date; 
if (option=="Last Month"): 	
	d2 = DateTime(d1.Year, d1.Month, 1).Date.AddDays(-1); 	
	d1 = DateTime(d2.Year,d2.Month,1).Date; 
if (option=="Last 12 Months"): 	
	d1 = d2.AddMonths(-12); 	 

print "Ideal dates: \t",d1,"\t",d2   

filt=Document.FilteringSchemes[0].Item[dt].Item[dt.Columns.Item[dateFilterName]].As[RangeFilter]() 
filt.Reset()  

#find lower and upper bounds from column 
lb,ub=set(),set()  

rows = Document.ActiveFilteringSelectionReference.GetSelection(dt).AsIndexSet()  

for r in rows: 	
	v=dt.Columns["Date"].RowValues.GetValue(r).Value 	
	if v.Date>=d1.Date:lb.add(v) 	
	if v.Date<=d2.Date:ub.add(v)  

print "Dates in dataset \t", min(lb),"\t",max(ub)  

filt.ValueRange = ValueRange(min(lb),max(ub))  

if option == "ALL":  	
	filt.Reset()    



''' 
Now create a document property called and have the option parameter point to it.  
Have this document property run this script when the property changes. You can copy and paste this values to your dropdown document property control: 
Today			Today 
Last 30 Days		Last 30 Days 
This Week		This Week 
This Month		This Month 
Year to Date		Year to Date 
Last Month		Last Month 
Last 12 Months		Last 12 Months
ALL			ALL 
'''

I created a radio button to drive the dropdowns for each method (via expression or via Iron Python)

The Radio Buttons have calculated values pointing to boolean document properties (sw1, sw2, sw3, and sw4, but you can add more as needed) Each calculated value has an on-click that toggles the boolean property off for all the switches and turns on the selected one via a parameter. The calculated value uses emojis to show the radio button on or off like this:

 If(${sw1},"-- W2D","⚪ W2D")

Finally, I attached a script when the switches changes. The script will pass the value of the document properties that drive the above dropdowns.

Final product preview