Spotfire Solution: Replacing Data with Different Named Columns while Using the Data Limiting Expression

In Spotfire, the filter panel allows one to easily remove ranges of values from your data.  We can gain even further granularity and control of what we hide from a dataset by applying the “Limit data using expression” window.  However, the “Limit data using expression window” doesn’t play nice when you want to replace a data table by matching columns with different names.

When we use replace data functionality and the limiting expression uses a matched column, the expression doesn’t update the column name (as it does with other expressions), which leads to unexpected results.  Call this one of those “endearing” Spotfire intricacies.

Fortunately, we can get around this issue by creating a Show/Hide calculated column and rerouting our limiting expression through a calculate column, which will update when you replace data.

So, what do I mean when I say that “Limit data using expression” doesn’t work when I replace a data table that uses a matched column?  The easiest way is to show you.

In a recent analysis, I wanted to display a Project’s value sums per year and scenario:

You can see above that no matter the Year or Scenario, the Project 2 sum is zero.  Seeing this one Project with zero values wasn’t an issue until I had many more Projects and zero values:

Multiple bars with zero began to override my visualization and obscure the Projects with actual positive values.

As mentioned before, this was easy to remove by right clicking the visualization and going to Properties > Data > “Limit Data Using” > Edit and using the following limiting expression:

In the above, [${WaterfallValue}] indicates the column represented in a dropdown menu called Value.  The brackets around it mean that we are calling the actual numeric values.  The over dictates that the sum is for the value total in each Project.

In English, the above expression says that if the sum for the chosen value for the project does not equal zero (!= 0), the Project will appear.  When trellising occurs, if that project’s sum does not equal zero across any of the trellis panels, all of them will appear, which is what we want.

Using the limiting expression resulted in:

Notice Project 2 is conspicuously absent.

However, then I tried to replace the data, matching columns that had different names but represented the same variables:

When we replace data, matching columns usually allows us to utilize the same visualizations.  We expect that [Project] call in the expression to automatically change to the new column in the expression.  However, on my visualization, I received this error:

Investigating further, the matching columns replacement worked in my Value axis expression:

  from before automatically changed to   

However, the data limiting expression was a different story:

 in the original led to 

The Project doesn’t update to [CaseProject] in the data limiting expression even though it does in the Value expression.  Thanks, Spotfire.

Solution

What to do, what to do?  I needed a solution to limit data but only my Category,  Value, and Calculated Column expressions take a zero—not the data limiting expression.  Well, what if we create a calculated column to utilize the name change, then reference that column—which will always keep the same name—in the data limiting expression?

I created a calculated column called “Show/Hide Flag”.   I then transferred the data limiting expression to a calculated column with a few adjustments as follows:

If the sum for a project does not equal zero, we will label it “Show” and if not “Hide”.  That [Project] reference of course successfully changes when I change the name to [CaseProject] since we are using a column and not a data limiting expression.  The above expression results in the following table:

We see Projects with all zeros are labelled “Hide”.

Then, in the data limiting expression, I used the expression [Show/Hide] = “Show”.  That expression says that if the column Show/Hide equals show, it will remain in the visualization.  An,d since it references nothing from the original dataset, it works!  We get the same view we obtained with our original limiting expression:

So, if you get stuck trying to use a data limiting expression and trying to replace data by matching names, try creating a Show/Hide column to maintain your limiting factor!

 

Jason is a Junior Data Scientist at Ruths.ai with a Master’s degree in Predictive Analytics and Data Science from Northwestern University. He has experience with a multitude of machine learning techniques such as Random Forest, Neural Nets, and Hidden Markov Models. With a previous Master’s in Creative Writing, Jason is a fervent believer in the Oxford comma.

One thought on “Spotfire Solution: Replacing Data with Different Named Columns while Using the Data Limiting Expression

  1. I believe that your solution may behave differently from the original implementation if you filter the data. The calculated column will evaluate whether to show without considering the filtering, whereas the initial “limit data” expression takes filtering into account. So if you filter out all non-zero rows for a project, I believe that project will still show up in the new solution, but not in the original implementation.

Leave a Comment

Your email address will not be published. Required fields are marked *