How can I exclude instead of include on a filter

This topic has 2 replies, 1 voice, and was last updated 1 month ago by Frank.

  • Author
    Posts
    • #297
       Frank
      Keymaster
      • Topics Started - 67
      • Replies Written - 52

      The example is that we have some product categories that we’d like to exclude from some of our views.
      In Stratum there are multiple ways to exclude data from a view. In a case where you’d like exclude specific data from a view for a dimension (in the example it will be product category) you have a few choices:
      1. Use an expression filter on the dimension
      2. Use an axis filter.
      3. Use a dynamic user list.

      Probably the simplest way to do this is to use an expression filter. To do this you’ll need to get to the Filter Type window. Depending on your version this may come up immediately when you go to filter on a dimension (older versions) or in the newer versions you’ll need to click on the More… button at the bottom of the filter window. Once you get to the filter type window select Expression and click ok.

      At this point you’ll need to have an expression in mind for excluding your stuff. In this example I’m trying to exclude an product categories that contain the word “Fresh”. You can find examples of different types of expressions in the Help. In the Help search for “axis filter example” and you’ll get some good examples, these can be used as a regular expression filter as I’m describing here, or they can be used as Axis filters (go figure:) ).

      So to continue with our expression filter – here is the expression for excluding any Product Category that has the word Fresh in it:

      //Exclude any Product Categories that have the word Fresh in them

      (INSTR(1, [Product Category].[Product Category].Properties(“PCat Long Description”), “Fresh”) = 0)

      The //Exclude any…. line is just a comment – you can add this to help you remember what/why you did this. Some of you may not need these kind or reminders, but I need to put things like this in to remind of what I was doing…

      The rest of the statement is pretty much a copy of what was in the help but I substituted my Product Category dimension.

      You can now drill to product category in my view and it will only show Product Categories that don’t have Fresh in them, or you can put Product Category in the View Filter and any information displayed in the view will only be for the total of Product Categories that don’t have the word Fresh in them.

      If anyone is interested in how you might use an axis filter or a dynamic user list to do this just post something here and I’ll respond.

    • #299
       Frank
      Keymaster
      • Topics Started - 67
      • Replies Written - 52

      Ran into a user that wanted to exclude a specific product by product code instead of something like the original example (of all Categories with the word Fresh in them). Thought I’d post the syntax for that. Everything else is pretty much the same as the original post.
      To exclude a specific product, by the product code or number (lets say the product code is 997653):
      [Product].[Product].CurrentMember.name <> “997653”

      Hope that helps!

    • #609
       Frank
      Keymaster
      • Topics Started - 67
      • Replies Written - 52

      New way to easily exclude stuff in a view! Part of our upcoming 7.2 release will be a new exclude option for dimensions. You can quickly specify if a filter is to be an include (the default like it is now) or exclude (new option coming in 7.2) If you read the earlier posts in this thread you’ll find a couple of examples on excluding specific values in a filter. This new change will make it even easier. Here’s a screen shot of the updated Filter window.

      Filter Window with Exclude

Viewing 2 reply threads

You must be logged in to reply to this topic.

CONTACT US

We're not around right now. But you can send us an email and we'll get back to you, asap.

Sending

©2020 KLEO Template a premium and multipurpose theme from Seventh Queen

Log in with your credentials

or    

Forgot your details?