Calculated Columns in a Table that respond to Filters

Introduction

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.

Code Sample 

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("]","")

 Document.Properties["DataLimiting"] = myDataLimiting

 case when Len("${StateFilterSelectedValues}")=2 then "1=1" 
 else "[State] in ${StateFilterSelectedValues}" end

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

 Sum([Sales]) OVER [State]

 Sum(case when ${DataLimiting} then [Sales] else 0 end) OVER [State]

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