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

# Fixed If

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

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

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

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

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

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

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