How to show filtered out values in a Text Area

Products	Versions
Spotfire Analyst	All Versions

Problem:

Solution:

There are scenarios when you may want to show which set of values have been filtered out and are therefore excluded from the data table on which visualizations are based. Using an expression like the following in a Calculated Value will only list the values which are current selected in the filter, and not the ones excluded:


Uniqueconcatenate([Region Values]) as [Region Values]

This is because those filtered out values are completely excluded and are not part of the data set used for the calculated value, and so this approach cannot be used. While you cannot create just a dynamic custom expression in a Calculated Value to retrieving such filtered out values, you can create few calculated columns first to achieve the requirement:

1. Create the first Calculated Column "ConcList" (see How to Add a Calculated Column). Concatenate the list of values in the column/filter whose excluded values you would like to show in the Text Area. For example:


Column Name: ConcList
Expression: Concatenate(UniqueConcatenate([RegionNew]),", ")

Note: This will add a comma space at the end of the list.

2. Create the second Calculated Column "Regex". Regular expression (see reference) needs to be used for extracting such excluded values from filter, so this calculated column expression is used to group each filter value followed by comma using brackets ().


Column Name: Regex
Expression: Concatenate("(",[RegionNew],",)")

3. Now create the Calculated Value in the Text Area (see How to Use Calculated Values) using the following custom expression:


Left(Trim(RXReplace(First([ConcList]),RXReplace(UniqueConcatenate([Regex]),", ","|","g"),"","g")),-1) then SN([Value],"All values are filtered out")

This regex will help you check if the filter value selected is part of the above list or not. If it is part of above list, replace it.

The Calculated Value will now show all values that have been filtered out for that column.

Explanation:

UniqueConcatenate([Regex]) - This will concatenate all selected filter values like (a,), (b,), (c,)....
RXReplace(UniqueConcatenate([Regex]),", ","|","g") - This will replace ", " in the above concatenated list of selected filter values with "|" like (a,)|(b,)|(c,)....
RXReplace(First([ConcList]),RXReplace(UniqueConcatenate([Regex]),", ","|","g"),"","g") - This will search for any value part of list of selected filter values in the concatenated list of all filter values, if found then those values will be replaced with blank
Trim() - This will remove any trailing spaces in the excluded values list
Left(,-1) - This will remove the comma from the end of excluded values list

Doc: Text Functions