3 Replies Latest reply on Jan 24, 2019 5:28 PM by Zhouyi Zhang

# IF THEN ELSE, ELSE IF - Long Calculated Field - Help Navigating Please

I have 2 tables - Both from the same data source

Table 1 is initially filtered to show me how many machines we've built.  This gives me an accurate # of machines built.  So this table is solid.

(the second table is the exact same information, just unfiltered (left join))

Table 2 is the same information, just unfiltered, and from this table I need to input a calculated field that identifies all my defects per machine

(the idea being that I can take (# of defects/# of Machines) to get my Defects Per Unit

There are many parameters that need to be met before I get to my # of defects

[CA ID] <> "0_defects"

[FA ID] <> "600", "700-706", "800", "900"

[Q/V] <> "V"

[Disposition] = null

So far I have multiple calculations that may or may not work... but I'm trying to get them into a single calculation:

*works*

IF STARTSWITH([Event Type-D], "0") THEN "Ignore"

ELSEIF CONTAINS([Event Desc-D], "defects") THEN "Ignore"

ELSE "Defect"

END

*works*

IF [CA ID-D]="0_DEFECTS" then "Ignore" ELSE "Defect" END

*does not work*

IF [FA ID-D]="600" OR "700" OR "701" OR "702" OR "703" OR "704" OR "705" OR "706" OR "800" OR "900"

THEN "Ignore" ELSE "Defect" END - "Can't logically "OR" boolean and string values"

*does not work*

IFNULL([Dispo Cd-D]) THEN "Defect" ELSE "Ignore" END - "Syntax error"

I've justed moved over from PowerPivot and I'm racking my brain against Tableau's logic, so I'm new to all this.  If you can offer any help or advice I would appreciate it.

• ###### 1. Re: IF THEN ELSE, ELSE IF - Long Calculated Field - Help Navigating Please

Hi, Stephen

IF [FA ID-D]="600" OR [FA ID-D]="700" OR [FA ID-D]="701" OR [FA ID-D]="702" OR [FA ID-D]="703" OR [FA ID-D]="704" OR [FA ID-D]="705" OR [FA ID-D]="706" OR [FA ID-D]="800" OR [FA ID-D]="900"

THEN "Ignore" ELSE "Defect" END

and

IF ISNULL([Dispo Cd-D]) THEN "Defect" ELSE "Ignore" END

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: IF THEN ELSE, ELSE IF - Long Calculated Field - Help Navigating Please

I Have a similar Problem .....

Hi,

I am trying to do a IF Then statement - currently looks like this: IF[Back order Qty]>[Curr E Res U Inv]THEN[Curr E Res U Inv]ELSE[Back order Qty]END

The problem is that "Curr E Res U Inv" is a calulated field : { FIXED [Product-Code]:MAX([Curr E reservation])} so that it doesnt inflate the reserved value.

The "Curr E Res U Inv" works perfectly, but when the IF formula returns the value, it does end up being inflated again as if it wasnt fixed.

I have tried amending it per the below, but the below formula is not acceptable and keeps giving errors please help.

IF[Back order Qty]>[{ FIXED [Product-Code]:MAX([Curr E reservation])}]THEN[{ FIXED [Product-Code]:MAX([Curr E reservation])}]ELSE[Back order Qty]END

Help would be greatly appreciated, Thank you

• ###### 3. Re: IF THEN ELSE, ELSE IF - Long Calculated Field - Help Navigating Please

Hi, Sharlene

If it is not a syntax issue, I will suggest to attached a sample workbook so that it will be easier to have a look.

ZZ