Jump to content
  • Date Range Expressions in Spotfire


    In this article 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.

    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

     


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...