3 Replies Latest reply on Jul 19, 2017 10:16 PM by Zhouyi Zhang

    Filter with Multiple Conditions

    Thiago Alpoin
    Thiago Alpoin Jul 19, 2017 1:01 PM

      Hi everyone,

       

      I'm having trouble to create a Condition by Formula in a filter before start my worksheet.

       

      So, not sure if I'm creating the wrong formula or if what I would like to accomplish, cannot be done in Filter by Formula. I would appreciate your help on this.

       

      here's my sample data:

       

      (attached)

       

      Client IdPricingPricing_FeaturesCurrent_Situation
      1121Ax, y, zYES
      1122Ax, y, zYES
      1123Ax, y, zNO
      1124By, zYES
      1125By, zNO
      1126By, zNO
      1127Cx, zYES
      1128Cx, zNO
      1129DzNO

       

       

      Before I start my worksheet, I would like to apply this filter, so my sheet only includes only IDs associates with the condition.

       

      2017-07-19_15-32-25.png

       

       

      Formula in 'english':

       

      IF [Pricing] has a [Pricing_Condition] that includes feature x

      AND [Current_Situation] = YES

      THEN [Client_Id] ELSE 0

       

      Ideal results - IDs:

       

      Client IdPricingPricing_FeaturesActive
      1121Ax, y, zYES
      1122Ax, y, zYES
      1127Cx, zYES

       

       

      However, I'm getting this message:

       

      2017-07-19_15-41-23.png

       

       

      Any recommendation of formula or suggestion how to pass this error message?

       

      Thank you so much!

        • 1. Re: Filter with Multiple Conditions
          Keith Troutt
          Keith Troutt Jul 19, 2017 1:58 PM (in response to Thiago Alpoin)

          Hi Thiago,

           

          Try this calculation:

           

          CONTAINS([Pricing Condition],'x')

          And [Current Situation] = 'Yes'

           

          Add it to your filter shelf and select true.

           

          Thanks!

           

          Keith

          • 2. Re: Filter with Multiple Conditions
            Thiago Alpoin
            Thiago Alpoin Jul 19, 2017 2:45 PM (in response to Thiago Alpoin)

            Hi Keith Troutt

             

            Thank you so much for your prompt reply and help on this.

             

             

            However (and my apologies for that as I may be not so clear on my question), that's not the solution I was looking for.

             

             

            My data is just a small sample of my database. My data is linked to a SQL server data, and I would like to extract the data before start my work in a worksheet.

             

             

            It's almost like a where clause so I can only work with the data that interests me so I can improve performance in my calculations.

             

             

            I was referring to filter in the data source tab before start my work in the worksheet. And that's where I'm getting the error.

            2017-07-19_17-44-18.png

             

            Any suggestion?

             

             

            Again, I highly appreciate your attention and help on this. This puzzle is killing me!

            • 3. Re: Filter with Multiple Conditions
              Zhouyi Zhang
              Zhouyi Zhang Ambassador Jul 19, 2017 10:16 PM (in response to Thiago Alpoin)

              Hi, Thiago

               

              Create 2 filters instead of combine them into one.

               

              Please see below steps:

               

               

              Result

               

               

              Hope this could help.

               

              ZZ