4 Replies Latest reply: Feb 23, 2012 10:33 AM by Rob Wunderlich RSS

    Several if-functions in formula (without sum/count etc.)

      I want to add several if-functions in my formula and I get stuck if I want to complete the formula

       

      E.g.:

       

      Country, Customer, CustomerOrders

      Netherlands, A, A5

      Netherlands, B, B7

      Netherlands, C, C8

      Belgium, N, N8

      Belgium, O, O5

      Belgium, P, P6

      Germany, A, A7

      Germany, R, R7

      Germany, S, S9

       

      The goal is to show all CustomerOrders for the Netherlands for Customers A + B, and for Belgium for Customers O + P, and for Germany for the Customers R + S

       

      I have a begin:
      if(Country = Netherlands and (Customer = A or Customer = B), CustomerOrders))...but then I get stuck.

       

      Can somebody help me with this formula?

        • Several if-functions in formula (without sum/count etc.)
          Rob Wunderlich

          How about

           

          if(match(Country & Customer,'NetherlandsA','NetherlandsB','BelgiumO','BelgiumP','GermanyR','GermanyS') >0

           

          -Rob

          • Several if-functions in formula (without sum/count etc.)
            Dennis Hoogenboom

            You could also adjust your script and create a unique field which you use only in your expression.

             

            Load:

            (Country &'-'& Customer) as CountryCustumerKey

            From ...

             

             

            This will create:

            field: CountryCustumerKey

                           Netherlands-A

                           Netherlands-B

                           Netherlands-C

                           Belgium-N

                           Belgium- O

                           Belgium-P

                           Germany-A

                           Germany-R

                           Germany-S

             

            This way you can use the CountryCustumerKey-field in your IF-statement.

             


            • Several if-functions in formula (without sum/count etc.)

              It is more difficult, then I described in the example, the data set is incorrect, my apologies for this.

               

              I want a sum of all customer orders which meets the conditions. ("The goal is to show all CustomerOrders for the Netherlands for Customers A + B, and for Belgium for Customers O + P, and for Germany for the Customers R + S")

               

              So if this is the dataset:

               

              Netherlands, A, 5

              Netherlands, B, 7

              Netherlands, C, 8

              Belgium, N, 8

              Belgium, O, 5

              Belgium, P, 6

              Germany, A, 7

              Germany, R, 7

              Germany, S, 9

               

              I want to see:
              Customer Orders = 39

              sum of (5+7+5+6+7+9)

               

              Thnx in advance,

              ZR