5 Replies Latest reply on Sep 27, 2017 5:41 AM by Jim Dehner

    Fixed If

    Alexander Hessler

      Hi guys,

       

      here is my problem. I have a data bank which contains "Product ID", "Order ID", "Categories" (contains only zeros and ones) and "Price".

       

      I aim at showing all prices of

      1. products with the category = 0 and price >=1000 Euro

      2. products with the category = 1 where the sum of the product prices within an order is >= 1000 Euro. That means, the calculated field for this category should not only summarize prices of products with exceed (or euqal) 1000 Euro, but also check whether the sum of products in an order exceed the price border.

       

      Here is my approch:

      1. I created a calculated field "sum order category1 price"

      {FIXED [Order ID]: SUM( IF [Category]=1 THEN {FIXED [Product ID]: MIN([Price])} END)}

       

      2. I created a second calculated field: "value>1000"

      IF [Price] >= 1000 AND [Category] = 0

      THEN { FIXED [Product ID]: MIN([Price]) }

      ELSEIF [sum order category1 price] >= 1000

      THEN { FIXED [Product ID]: MIN([Price]) }

      ELSE NULL

      END

       

      The problem is that once the sum of all products where category=1 within one order exceed 1000Euro, also products of category=0 (and price<1000) are included.  In my dataset, this can be seen when looking at Product ID 14.

       

      In case you know a solution, please also explain what I did wrong

        • 1. Re: Fixed If
          Jim Dehner

          Hi

           

          If I understand what you are trying to do the logic in you >1000 cacluation second clause needs to check again for Category=1

           

          the fromula would read

          IF [Price] >= 1000 AND [Category] = 0

          THEN { FIXED [Product ID]: MIN([Price]) }

          ELSEIF

          [Category] = 1 and

          [sum order category1 price] >= 1000

          THEN { FIXED [Product ID]: MIN([Price]) }

          ELSE NULL

          END

           

          sww the image below - it is your chart converted to a cross tab and the lase row is the revised calcualtion

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Fixed If
            Alexander Hessler

            Hi John, thank you for your answer. You're right. Now it works

             

            But can you explain why this Category=1 condition is not already fulfilled by the first calculated field? Because there I aim at summing up only values in category=1. So why does it include the 200 Euro (Item ID 14) ? It is supposed to work like a sumif function in excel.

            • 3. Re: Fixed If
              Jim Dehner

              Thanks - glad to help out

              The first clause in the IF statement has 2 conditions than BOTH must be satisfied so it the Category =1 OR if the Price <1000 then the fromula returns a False (i.e. your Category =0 and Price <1000 records get passed to the second clause) - Now the second clause will summ all the records pasted to it that are on the same order - that ould include the Category 1 products you wanted and the Category 0 products that go through the filst clause that you don't want

               

              Jim

              • 4. Re: Fixed If
                Alexander Hessler

                Ok, thank you for the explanation!

                But again, I thought the first calculated field: {FIXED [Order ID]: SUM( IF [Category]=1 THEN {FIXED [Product ID]: MIN([Price])} END)}

                would cover the case category = 1 ?!  Because my target here is that only category=1 will be summmarized and category=0 should be empty in this field. If this worked, then the second field would not need an AND statement for category=1 because there are only items of this category.

                 

                I hope you understand my problem. I just want to understand my mistake..

                 

                Alex

                • 5. Re: Fixed If
                  Jim Dehner

                  Hi

                  Yes I do see that you had 2 separate calculated fields - they are independent - one does not affect the other -

                  You second If statement is where you had the problem with the total order including data you did not want

                   

                  Jim