Calculated Columns in a Table that respond to Filters

This article contains tricks for capturing selected filter values, writing them to a document property, and using that property for data limiting.

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

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

Sign In

Calculated Columns in a Table that respond to Filters

Introduction

Code Sample

Attachments

Table of contents

User Feedback

Recommended Comments

Industries