A few weeks ago, I wrote a post on data limiting in Spotfire when column names change. I provided solutions using both data limiting with expression and the Show Hide Items function. I struggled to get working solutions in both because I expected them to work the same way, but they do not. Therefore, this week, I developed a comprehensive tutorial to show you what they can do and how they are different. Read on to learn more.
Blog or Video
Data Limiting With Expression
Users limit data with an expression in the Data menu located in the Properties dialog of any Spotfire visualization. This is one of my favorite ways to limit visualizations, and I’ve also written about it here.
Spotfire evaluates each record in the table as true or false relative to the expression. The expression below says ‘Actual Tax is not equal to zero.’ Therefore, if the value in the Actual Tax column is not equal to zero, it will be shown in the visualization. If equal to zero, it will be excluded. Super simple.
[Actual Tax] != 0 The Important Part
Notice that data limiting with expression DOES NOT require aggregation because it’s evaluating each record. As you will see, this differs distinctly from Show Hide.
Show Hide
Show Hide has it’s own menu in the Properties dialog of every visualization, which allows users to select from a number of different rule types to limit data in a visualization.
Before I explain each of the rule types, I want to point out that some visualization types, like bar charts or line charts, will default to [Value axis values] when creating rules. TIBCO added the Value axis values option a few upgrades ago as a way of making Show Hide more dynamic. When Value axis values is chosen, you can change the column of data on the Y-axis. The rule will update and apply to whatever column and aggregation are set on the Y-Axis without having to go into the menu to update the rule. Solid feature upgrade!
Rule Types
The “Top” and “Bottom” rule options are super handy and much easier to use than trying to get the same result in data limiting with expression. For example, “Top” and “Bottom” could allow you to show or hide “Top 5 Gas Producing Wells” or “Bottom 5 Gas Producing Wells” if individual wells were on the categorical axis of a bar chart or cross table. Be careful when using Top and Bottom with stacked bar charts. Spotfire looks at the smallest segment on the chart, which is going to be each segment of the bar. Top and Bottom will also show more than your top selection if the values are the same.
Next, all of the rule types between “Between” and “Less than or equal to” essentially work the same as data limiting with expression. And, after choosing the rule type, the user may enter a static value or choose to apply a calculated value to the rule logic.
However, there is one very important difference between Show Hide and data limiting with expression, which I will get to in just a sec. But first, I want to explain the Boolean expression rule.
Boolean Expression Rules
Applying Boolean expression rules is a bit tricky. Before attempting it, select a column of data to apply the rule to. Then, click on the pencil to create an expression, such as the example shown below.
After clicking the pencil, when the custom expression dialog opens, you’ll notice the only available column is ‘Axis.Value’. What are you supposed to do with this? Well, you’re supposed to write a boolean expression using Axis.Value. Axis.Value refers to the value output from the expression you’ve already typed in, which in this case is “Sum([Actual Opex])”.
Thus, with the configuration shown above, Spotfire will show items where the sum of actual opex is greater than 500,000.
Now (drum roll), let’s talk about the key difference between Show Hide and Limiting with Expression.
The Difference Between Show Hide and Limiting with Expression
When you select a column of data in Show Hide, you MUST also select an aggregation method.
This is important because Show Hide applies rules to the visualization, not each record. And generally speaking, visualizations roll up data. Therefore, Show Hide requires an aggregation. Let me show you an example.
Below, I have two cross tables. One of them includes the Well and the Field on the vertical axis. The other just the Field. If I apply a rule to both visualizations that says show data where the sum of Actual Opex is greater than 400,000, one record will be removed from the top table, and the bottom table will not change because the rule is applied to the visualization, not each record behind the visualization.
This is a subtle but very important distinction between Show Hide and data limiting with expression. This is what I was working on in the post — Data Limiting When Column Names Change. I wanted to prove that you could use data limiting with expression or Show Hide to get the same result. I struggled because I didn’t realize one required aggregation and the other did not.
To explain, one of the cross tables below uses data limiting with expression and the other Show Hide Items. They are both connected to a list box multi-select property control called “ListBoxMulti” to set the columns. You can see my custom expressions differ only in the aggregation.
Show Hide: $map("Sum($esc(${ListBoxMulti}))!=0", " and ")
Data Limiting with Expression: $map("($esc(${ListBoxMulti}))!=0", " and ")Conclusion
Whew, this has been a long post. I didn’t really know what I was diving into. To summarize, Show Hide and data limiting with expression both allow the user to customize what appears or disappears from a visualization, but they perform that task at different levels. Show Hide works with the vertical axis configuration and aggregates data. Limiting data with expression works at the record level. If you found this post useful, please repost or reshare. Thank you and have a great week!
Spotfire Version
Content created with Spotfire 10.3.