How to Limit Data using an Expression in Spotfire

Have you ever wanted to limit the data displayed in a visualization without using filters?

Most of the time your Spotfire projects have multiple graphs on a page, with each one serving a different purpose. Let’s say you want one of those charts to limit data more than the filter settings, like a specific time period (ex: “This year’s production”).

Your first thought might be to create a new filtering scheme for that chart, but there’s an easier way! Multiple filtering schemes can get cumbersome to keep track of.

Instead of creating a custom filtering scheme for one chart, just limit the data with an expression.

The example below has 2 graphs and a cross table. The bar chart and the table are showing all data. However, the line chart is showing a more detailed view, just of the year 2015.

[ninja-popup ID=1206] Download the Spotfire template shown in the screenshots [/ninja-popup]

1_-_Spotfire_Data_Limited_by_Expression

How did I do this? By simply limiting the data shown in the bottom graph to the year 2015 with a custom expression.

Options for Limiting Data in a Visualization

In a given visualization, there are several different ways that we can limit data. By “limit data”, I mean “don’t show it on this chart, but show it everywhere else”.

You can limit data with:

  • Markings (details visualization)
  • Filters
  • Custom expressions
  • Show/Hide Items
  • Subsets
2.1 - Spotfire Data Properties 3.1 - Spotfire Data Properties
4.1 - Spotfire Show Hide Items Properties 5.1 - Spotfire Subsets Properties

Steps to Limit Data with an Expression

In my example, I’ve limited data using an expression, specifically when the Year = 2015. To do this, follow the steps below.

Steps:

  1. Open the visualization properties (right click on the visualization)

6 - Spotfire Properties

  1. Click the Data tab
  2. Scroll down to “Limit Data Using an Expression”
  3. Click Edit

7 .1- Spotfire Edit Data Limiting Expression

  1. Enter your custom expression. The expression I used is: Year([Date])=2015

8.1 - Spotfire Limit Data with Expression

  1. Click OK, then Close

That’s it! Play around with different functions in your expressions to show the data you need.

Notice that the legend now has a “Data Limiting” Section to remind you and others that the chart is not displaying all data.

9 - Spotfire Before After Data Limiting Expression

Questions / Comments
If you have any questions or comments, or if there’s something you’d like featured on this blog, feel free to email me at kyle@datafuel.co¬†or leave a comment below.

[ninja-popup ID=1206] Click Here to download and follow along with the Spotfire example shown in the article! [/ninja-popup]

19 thoughts on “How to Limit Data using an Expression in Spotfire

  1. Adrienne Reply

    Hi Kyle- What would be the syntax for multiple years? Say 2013-2015.

    • Kyle LaMotta Post authorReply

      Hi Adrienne –

      You could write it as: Year([Date])>=2013

      Or if you just wanted those 3 years (2013, 2014, 2015) and didn’t want to include 2016: Year([Date])>=2013 and Year([Date])<2016

  2. Varun Ganesh Reply

    Hi Kyle,

    I would like to restrict data using values that are matching pattern say for example
    if argument like %/Foods% and status like %Finished%. How would you do this in Spotfire ?

    • Kyle LaMotta Post authorReply

      Varun,

      If those columns don’t exist in your data, I would suggest inserting them as calculated columns. For example, you could create a “Category” column to define the values:

      Case when [item] = “milk” or [item] = “bread” or [item] = “butter” then “Food” else “Some other Category” End

      Just add ‘When’ lines to the case statement above to define all the categories. You can also use the Group By feature in Spotfire to quickly group these together. Then to limit data in a visualization, you could use the following expression (replacing the values in brackets with your column names):

      [Category]=”Food” AND [Status]=”Finished”

      Note that the values in quotes above are case-sensitive.

      Hope this answers your question, if not let me know!

  3. Jessica Reply

    Kyle, is there a way to have multiple expressions that limit the data? I am having trouble finding a syntax that will allow me to have multiple expressions.

    Thanks

    • Kyle LaMotta Post authorReply

      Jessica – You can only have one data limiting expression for each visualization, but each expression can have more than one statements or conditions that are being evaluated to limit. If you have a specific example you’re looking at, I can help come up with an expression.

  4. PARAMJIT SINGH Reply

    HI Kyle,

    What syntax t use if you want to select data from the previous 12 months relevant to the current month.

    Thanks,

    Param

    • Kyle LaMotta Post authorReply

      Param,

      Here’s the syntax to only show data for the 12 months prior to the current month:

      [Date] >= DateAdd("month", -12, DateTimeNow())

      -Kyle

  5. Swapnil J Reply

    Hi Kyle,
    How do I pass multiple values to a column? Thanks!
    For example,
    Country = (“USA”,”FRANCE”)

    • Kyle LaMotta Post authorReply

      Swapnil, this should work: [Country]="USA" or [Country]="France"

  6. Steve Reply

    Is there a way to have an express change the limit only if no marking is used? I have a pie chart that changes depending on the selected week. But, if there is no week selected in my marking, I’d like for it to show the most current week in the table.

  7. Thiago Reply

    Good Night!
    How make for execute these expressions with conditions?

  8. John Klein Reply

    Hi Kyle – helpful post. But when I use this feature it still shows the unlimited number of rows on the status bar – eg 1234 of 1234 Rows. Would be nice if either the first or second number changed. do you know a way to do this. Thanks, John

    • Kyle LaMotta Post authorReply

      Thanks, John!
      The row count in the status bar is only counting the number of filtered rows. Limiting the data on the chart with an expression does not affect filtering, so this number doesn’t change. That’s why the chart legend gets updated to show “Data limiting: Expression” – to point out that not all data is being displayed, even if nothing has been filtered out. If you want to count the number of rows in a chart after adding a data limiting expression, you can mark all of the data in that chart (click and drag your mouse over all values) and look at the Marked row count in the status bar.

  9. Anonymous Reply

    How to make the value dynamic so that the data gets limited based on user inputs?

  10. Anonymous Reply

    I am trying to combine verbiage in free text field. For example, rate change, changed rate, modify rate, lower rate, rate reduced etc.
    How to create a calculated column to make all these one type, which should be “Rate”?

  11. Matt Reply

    Kyle, I have two “List Box Multiple Select” properties in a text box that I want to incorporate into the Limit Data By Expression feature. What would be the syntax?

    I’ve tried

    “$map(“${Property1}”, “,”)” ~= [Column A] and “$map(“${Property2}”, “,”)” ~= [Column B]

    while substituting ” , “, ” ; “, ” + ” and some others for “and” in the above expression and it doesn’t like any of them. I can get where I need to easy enough by just filtering in this case but wondering if it’s possible.

Leave a Reply

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