4 Replies Latest reply on Sep 12, 2019 9:14 AM by Sofia Bonilla

    IF and ONLY IF/ Condition Filter

    Sofia Bonilla

      Hi I am trying to figure out how to do an 'if and only if' kind of filter. I can't share the data that im working with but for purposes of my problem we will pretend a customer can  buy Apples, Oranges and Bananas. How can I create a filter to show me customers that have [CASE1] ONLY bought Apples or [Case 2] ONLY Apples and Oranges? When I filter out lets say Bananas it will show me the customers that have bought apples and oranges [including those that bought bananas too] even though the quantity of bananas is not showing.

       

      Data looks like this:

       

      Customer     Fruit             QTY

      A                  Apple               1

                          Oranges           2

       

      B                  Apple               3

                          Banana            2

       

      C                 Banana             2

                          Oranges            1

                          Apple                1

       

      D                 Apple                 4

       

      E                  Banana             3

       

      F                   Apple               1

                           Oranges           2

       

      G                  Oranges          3

       

       

      * Marked with colors the customers that should be kept when applying the filters in each 'Case'

       

      Thank you in advance and Happy Analyzing

        • 1. Re: IF and ONLY IF/ Condition Filter
          swaroop.gantela

          Sofia,

           

          As you've seen, the issue at hand is that it's hard to pull out what exactly the user

          has selected in the filter.

           

          I tried a very round about way, it probably didn't get there, but maybe it can give ideas.

           

          First is to count how many fruits truly per Customer:

          { FIXED [Customer]:COUNTD([Fruit])}  //this ignores any filters

           

          Then to count how many fruits after the filter selections

          WINDOW_COUNT(COUNT([Fruit]))

          //this requires table calculation setting by Customer and Fruit at the Deepest Level, restarting every Customer

           

          I think this next part has the assumption that at least one customer will meet the critieria, but I'm not sure.

          It calculates what is the max number of fruits that any customer has, given the filter selection:

          WINDOW_MAX([Count Fruit after Filter])

          //table calculation setting of table down

           

           

          So in addition to your fruit filter, there is a second filter of:

          MAX([Fruit Count Per Customer])=[How Many Selected]

          AND

          [Count Fruit after Filter]=[How Many Selected]

           

          Which I think will filter to the needed customers.

           

          Please see workbook v10.3 attached in the Forum Thread

          IF and ONLY IF/ Condition Filter

           

           

          315568filter.png

          • 2. Re: IF and ONLY IF/ Condition Filter
            Joshua Milligan

            Hi Sofia,

             

            If the "Fruit" field in your actual data has a limited number of values (low cardinality) like this sample set, then you could create a calculation per value to determine if a customer had purchased that fruit.  For example, create a calculation called Customer Purchased Apples:

             

            {FIXED [Customer] : MIN(IF [Fruit] = "Apple" THEN 1 END) } == 1

             

            Will return true for each customer who purchased an apple.  Duplicate that calculation for each value of Fruit and you'll have the building blocks for any logic you want:

             

            [Customer Purchased Apples]

            AND

            NOT [Customer Purchased Oranges]

            AND

            NOT [Customer Purchased Bananas]

             

            -----------------

             

            If you have quite a few values that you actually care about then it gets tedious.  One approach, if you want to know customers who only purchased one specific type of fruit would be to use a simple aggregate calculation:

             

            MIN([Fruit]) == "Apples" AND MAX([Fruit]) == "Apples"

             

             

            At a customer level, that's only true if they only ever bought apples.

             

            --------------

             

            If you have a lot of values and need it to be very dynamic, there are some different approaches you can take and I'd be happy to take a look at a more detailed mock-up of data if that's the case!

             

            Hope that helps!

            Joshua

            2 of 2 people found this helpful
            • 3. Re: IF and ONLY IF/ Condition Filter
              Zhouyi Zhang

              Hi, Sofia

               

              Not sure if I understood your requirement correctly or not, but probably you can try something like below.

               

              create a calculation filed as filter as shown below, and drag drag it to filter and select true (ps. you have to use ATTR() as it comes from the measure)

              e.g. if Apple and Orange are selected, only A and F displays,

               

              or if only apple is selected, show D only

               

               

              or if all is selected, then show C only?

               

              If above are the scenarios, please find the attached sample workbook.

               

              Hope this helps

               

              ZZ

              2 of 2 people found this helpful
              • 4. Re: IF and ONLY IF/ Condition Filter
                Sofia Bonilla

                Thank you so much Zhouyi Zhang. Your calculation works great with what I needed.

                 

                Have an amazing week and keep on killing it