Jump to content
  • Calculated Columns in a Table that respond to Filters


    Always thought this couldn't be done, and such is the answer to all of the questions about it in the community.  Finally figured it out so am sharing here.

    The trick is to capture the selected filter values and write them to a document property, then use that document property in your data limiting.

    The below code takes the filter values selected for column [State] and writes them to a string document property "StateFilterSelectedValues".

    The only parameter used is myDataTable which is a Data Table parameter (i.e. the table where [State] column exists).

    from Spotfire.Dxp.Application.Filters import ListBoxFilter
    from System.Collections.Generic import List
    from Spotfire.Dxp.Data import *
    
    list_data = List [str]();
    valData = List [str](set(list_data))
    
    #get a reference to a listbox 
    filt = Document.FilteringSchemes[Document.ActiveFilteringSelectionReference][myDataTable][myDataTable.Columns["State"]].As[ListBoxFilter]()
    
    
    #loop selected values
    for value in filt.SelectedValues:
    	list_data.Add(value)
    
    
    print  str(list_data).replace("List[str]","").replace("[","").replace("]","")
    
    Document.Properties["StateFilterSelectedValues"] = str(list_data).replace("List[str]","").replace("[","").replace("]","")
     
    On change of Document property "StateFilterSelectedValues" we trigger the below script which writes our data limiting expression as a string to a second document property named "DataLimiting"
     
     Document.Properties["DataLimiting"] = myDataLimiting
     
    Script parameter myDataLimiting is a string set with expression 
     
     case when Len("${StateFilterSelectedValues}")=2 then "1=1" 
     else "[State] in ${StateFilterSelectedValues}" end
     
    We do this second part because our "StateFilterSelectedValues" document property is populated with something like ('NJ', 'CA', 'NY') or () and that causes issues when trying to use it directly in a visualization data limiting.

    Finally, in our Table calculated column, instead of writing something like 

     Sum([Sales]) OVER [State]
     
    We instead write 
     
     Sum(case when ${DataLimiting} then [Sales] else 0 end) OVER [State]
     
    So only the filtered values are included in the calculated column.

    Sample dxps attached in Version 10.3 and 7.11 attached.  You might have to trust scripts for it to work

    Attachments

    Download Attachments From Resources

    capture_filter_selections_in_document_property_v7.dxp

    capture_filter_selections_in_document_property_v10.dxp


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...