9 Replies Latest reply on Jun 21, 2018 1:41 PM by Deepak Rai

    Multiple Conditions

    Jane Abc

      Hi there,

       

      Can anybody answer my question?

       

      Criteria is total Category level should be more than $1000 and Supplier level should be less than $1000

      If Category >1000 and Supplier <1000 then get sum of the PO order

      Below is the simplified sample:

      Category "a" can't be  since it has more than $1000

      Category "c" can;t be even though suppliers' less than $1000 but total is less then $1000 for category

      Category "d" can't be since Category level is less than $1000 even though supplier level meets our criteria

      Category "b" is the one we are looking because total is $1300 more than $1000 and each supplier is less than 1000

      Category "e" also meets our criteria

      So, category "b" and "e" will be in my table with sum of PO orders and in the second table when i press "b" category it should bring me to company names and their balance.

      Data should be fixed but it keeps changing when i filter to at least 100000 and bring suppliers...

      categorysupplierPO order amount
      a

      zxy company

      1100
      babc company400
      aasd company520
      cert company650
      csd company100
      bzxy company300
      bzxy company600
      dsdf company800
      ezxy company500
      esd company600
        • 1. Re: Multiple Conditions
          Jaime Lego

          Have you tried creating two fixed calculations for total category and total supplier and then referencing those in your IF calculation?

           

          [Total Category Order Amount]

          { FIXED [Category]:sum([Order Amount])}

           

          [Total Supplier Order Amount]

          { FIXED [Supplier]:sum([Order Amount])}

           

          IF [Total Category Order Amount]>1000

          AND [Total Supplier Order Amount]<1000

          THEN [Order Amount]

          ELSE 0

          END

          • 2. Re: Multiple Conditions
            Joe Oppelt

            Two LOD calcs:

             

            { FIXED [Category] : SUM( if [PO Order Amount] > 1000 then 1 else 0 END ) }

             

            { FIXED [[supplier] : SUM( if [PO Orderr amount] < 1000 then 1 else 0 END ) }

             

            For each row, if both calcs are 1 then you have a row you want to use.

             

            If you can't get this to work, upload a sample workbook.

            • 3. Re: Multiple Conditions
              Deepak Rai

              You have a,b,c in this data because one of the supplier for a is less than 1000. Pl check screenshot and attached.

              Thanks

              Deepak

               

              1 of 1 people found this helpful
              • 4. Re: Multiple Conditions
                Jane Abc

                Hi Deepak,

                 

                Appreciate your response; however, i am still getting errors.

                In your suggestion above,I don't want to include category "a" since zxy company has more than $1000 so Category a's supplier is more than 1000.

                I want to just see categories more than $1000(total) with condition each supplier in that category not to exceed $1000. So, in my example above i just want to see category "b" and "e".

                Is there a way to sum up total suppliers for one category? e.g. sum of the total suppliers value for category "a"?

                Thanks a lot

                • 5. Re: Multiple Conditions
                  Jane Abc

                  Hi Jaime,

                  Appreciate your response; however, i am still getting errors.

                  I tried to apply your formula but it is still not working

                  I want to just see categories more than $1000(total) with condition each supplier in that category not to exceed $1000. So, in my example above i just want to see category "b" and "e".

                  Is there a way to sum up total suppliers for one category? e.g. sum of the total suppliers value for category "a"?

                  Thanks a lot,

                  Jane

                  • 6. Re: Multiple Conditions
                    Deepak Rai

                    Here it is, Just Changed the Calculation to give u what u r looking for.

                    Thanks

                    Deepak

                     

                    If it Helps, Pl mark it Helpful and CORRECT to Close Thread

                    1 of 1 people found this helpful
                    • 7. Re: Multiple Conditions
                      Deepak Rai

                      Hi Jane,

                      If it Helped You, Can you Please Close This Old Thread To Help Someone looking for same answer by marking my Reply as CORRECt. The CORRECT Button is under my Reply in your this main Thread, Not in email

                      Thanks

                      Deepak

                      1 of 1 people found this helpful
                      • 8. Re: Multiple Conditions
                        Jane Abc

                        This is great! Thanks a lot! Much appreciate your help!

                        • 9. Re: Multiple Conditions
                          Deepak Rai