4 Replies Latest reply on Apr 8, 2016 4:16 AM by Alexis Jensen

# Calculation to return value based on values in other fields

Hi All

I have a data set similar to the sample table I have shown below.  I need to be able to return the value in the yield field based on the values in product group and component.  Eg: If product group = "Product 1" and If Component = "Component B" then return 2.3.  This value will be used in another calculated field.

 Product Group Component Yield Product 1 Component A 1.5 Product 1 Component B 2.3 Product 2 Component A 1.8 Product 2 Component C 2.0

Is my best option to use CASE statements, Nested IF statements or something else?  Or is a nested LOD expression the best way to go?  I will be using several of these, so want to find the most efficient solution.

I really appreciate your knowledge and help.

Regards,

Alexis

• ###### 1. Re: Calculation to return value based on values in other fields

Dear Alexis - As per my understanding, the given dataset is like a dimensions that you want to apply to your transactions. Let me explain. This is similar like the following

PRODUCT GROUP     STATE     TAX%

TABLES                         CA          5%

CHAIRS                         CA          6%

PHONES                        NY          9%

TAX % is a function of Product Group and STATE Combination

I would recommend, don't write case statement , nested if , nested LODs, everything will be static in nature. Instead have the data and loaded into an Excel or a Table. Then bring that table / Excel to Tableau and blend this with your actual Transaction dataset. Blend key should be PRODUCT GROUP and STATE together.

This will give you more flexibility to dynamically update or modify the base logic.

Thanks,

Manideep

• ###### 2. Re: Calculation to return value based on values in other fields

Hi,

In my table, Yield is not a calculated function of the other 2, I just want to call the value of Yield based on the values of product group and component for the purposes of a further calculation.

Thanks,

Alexis

• ###### 3. Re: Calculation to return value based on values in other fields

Hi Alexis,

You can use LOD expression to get this. try this

Mahfooj

• ###### 4. Re: Calculation to return value based on values in other fields

Thanks... great to learn new things