4 Replies Latest reply on Jan 29, 2019 7:33 AM by Ivona Karteva

# use several IF functions in one calculated field + using LOD function

Hey guys,

I know how  i can reach the target i want by creating two calculated fields with two functions however i want to combine them in one.

Below are two functions which work when making them separately but not together in one calculated field

IF [size1 (shelf_stock)]= 0 THEN{ INCLUDE

[id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

MAX([quantity (shelf_stock)])*AVG([average_price]) }

2.

IF [size1 (shelf_stock)] is different than 0 THEN

{ INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

MAX([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) } END

Does this make sense?

• ###### 1. Re: use several IF functions in one calculated field + using LOD function

well the syntax is not correct

this is closer

IF [size1 (shelf_stock)]= 0 THEN{ INCLUDE

[id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

MAX([quantity (shelf_stock)])*AVG([average_price]) }

else

{ INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

MAX([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) } END

but this is very inefficient in processing - comput the LOD's out side the conditional then just place the value in the conditional - computing inside the conditional is done one at a time versus doing it all at once when calculated outside the conditional

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.

• ###### 2. Re: use several IF functions in one calculated field + using LOD function

I am using INCLUDE as you Used, But you can use FIXED also:

{ INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:MAX(IF [size1 (shelf_stock)]= 0 THEN ([quantity (shelf_stock)])*AVG([average_price])ELSE ([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) END}

• ###### 3. Re: use several IF functions in one calculated field + using LOD function

Hey Ivona,

If the two calculations are working, then I believe the following should work:

IF ([size1 (shelf_stock)]= 0 THEN{ INCLUDE

[id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

MAX([quantity (shelf_stock)])*AVG([average_price]) } END)

ELSEIF ([size1 (shelf_stock)] <> 0 THEN

{ INCLUDE [id_color (total_quantities)],[id_goods_group (total_quantities)],[id_article (total_quantities)]:

MAX([quantity (shelf_stock)]*[size1 (shelf_stock)])*AVG([average_price]) } END)

END

Hope that works. If does please mark it as helpful and correct so other users can refer to it.

Best,

Diego

• ###### 4. Re: use several IF functions in one calculated field + using LOD function

This just popped out in my mind but somehow it doesn’t work.

I will have to check the database and I might be doing something wrong choosing the wrong data fields.

Ivona Karteva

Supply Chain Manager

email: ivona.karteva@teolino.eu

phone: +35932904905

mobile: 0886707257

Teolino Plast LTD

Plovdiv, Bulgaria