1 Reply Latest reply on Oct 25, 2016 10:15 AM by Michael Lowden

    Filters in AND mode instead of OR??

    Michael Lowden

      This may be a dumb question from someone that is just new, and frankly I hope it is. My problem is simply that I can't figure out how to get a Filter to act as an AND restriction instead of OR.

       

      ex:

      SALES REP
      INVOICE COUNTRYINVOICE AMOUNT
      SteveUSA100
      SteveCanada100
      MikeUSA100
      MikeMexico

      100

      MikeGermany100

       

      When I put a filter for Country and choose USA and Canada I see both Steve and Mike. Which is logically and OR. Country=USA OR Country=Canada. But what I want is to only see sales reps with actual sales in both countries. So in this example I'd only see Steve. logically it would be: Country=USA AND Country=Canada.

       

      What I get by default, an OR:

      Country = USA, Canada

      What I want as an AND:

      Country = USA, Canada

      SALES REPINVOICE COUNTRY
      SteveUSA
      SteveCanada
      MikeUSA
      SALES REPINVOICE COUNTRY
      SteveUSA
      SteveCanada

       

      The catch, the number of countries selected is unpredictable. The only idea I have right now is to have 4 or 5 country parameters and my own calculated dimension for filtering.

       

      thoughts?

       

      Thanks. --Mike

        • 1. Re: Filters in AND mode instead of OR??
          Michael Lowden

          The more I ponder this, the more I realize it's going to need an extra level of context awareness to function. And likely a whole VIEW or S.Proc to make it work in Tableau. If I could do a multi-valued list parameter and actually reference it, like in other products (like Crystal Reports), I'd do something like:

           

          define:

          - parameter InputList as ['USA', 'Canada']

           

          filter:

          WHERE [Country] IN InputList

          AND

          HAVING COUNT(DISTINCT [Country]) >= LENGTH( InputList )

           

          This way, in the example of Mike only having 'USA' in the list would be dropped, thus a sub-count of only '1'. I know the logic above is still not whole, but I hope you get the idea.