12 Replies Latest reply on Jul 1, 2013 11:08 PM by Steve Martin

# Translating Excel formulas - IF AND

Morning All,

Possibly a really simply question for some, but coming from a rookie Tableau user, I'm hitting walls trying to figure it out!

I'm trying to create a calculated field which creates alters the value in [LP] if the condition is true.

My normal formula, in access or something, would look like this ...

if(and([LP'='RV',[Component]="MBROOK'), "RB",[LP])

The following code is 'valid' in Tableau, but when I add the calcuated field pill to my row data, I get 'Null' in all rows.

if([LP]='RV' and ([Component]='MBROOK')) THEN 'RB' ELSE [LP] END

What I want, essentially, is to set the LP to RB where the LP is RV and Component equals MBROOK, and leave all other LP's as normal if they dion't equal RV.

Georgia

• ###### 1. Re: Translating Excel formulas - IF AND

Capitilization? Do LP and component have values as expected?

• ###### 2. Re: Re: Translating Excel formulas - IF AND

I agree with Alex.  Try this:

```if(UPPER([LP])='RV' and (UPPER([Component])='MBROOK')) THEN 'RB' ELSE [LP] END
```
• ###### 3. Re: Re: Translating Excel formulas - IF AND

Thanks for your help with this, on reflection, I had a blonde moment and wasn't linking across to the right corresponding field.

However, my next problem ...

My code is currently set to ...

if([LP]='RV' and ([Component]='M BROOK' OR [Component]='NCIG Type 1')) THEN 'RB'

ELSEIF([LP]='BF' and ([Component]<>'BFLD SP' OR [Component]<>'BFLD STM' OR [Component]<>'BFLD STR' OR [Component]<>'RATHLUBA' OR [Component]<>'BFLD TSS' OR [Component]<>'BFLD TTH'))

Then 'DS'

ELSEIF([LP]='BF' and ([Component]='BFLD SP' OR [Component]='BFLD STM' OR [Component]='BFLD STR' OR [Component]='RATHLUBA' OR [Component]='BFLD TSS' OR [Component]='BFLD TTH'))

Then 'BF'

Else [LP] END

For some reason, I had to add in the second section to actually include the 'BF' data, however, when the results come back, BF still has a 'Null' next to itself. Why is it simply not showing 'BF' and returning values??

• ###### 4. Re: Re: Re: Translating Excel formulas - IF AND

A packaged workbook to look at would help but this is getting beyond my developer skills.  I would recommend trying the IN() function (and maybe NOT IN() too) instead of the list of ORs.

```...ELSEIF([LP]='BF' and [Component] IN('BFLD SP', 'BFLD STM', 'BFLD STR', 'RATHLUBA', 'BFLD TSS', 'BFLD TTH'))
```
1 of 1 people found this helpful
• ###### 5. Re: Re: Re: Translating Excel formulas - IF AND

Hi Toby,

Thanks for that, I hadn't reaslied you could do In and Not In functions in Tableau.

I'll try that this morning and see how I go!

Regards,

Georgia

• ###### 6. Re: Re: Re: Translating Excel formulas - IF AND

Thanks to Alex Kerin and Toby Erkson for your assistance - turns out I

- can't use the In and Not In functions in Tableau

- have to use all listed components, separated by 'OR' ... to distinguish between one LP and another.
very clunky, but solution achieved!

• ###### 7. Re: Re: Re: Translating Excel formulas - IF AND

The IN() function didn't work?  That's odd because it's a valid function.  Hmm.  I'd have to play with that one myself...sorry it didn't work out for you   Glad you got if fixed, though.

• ###### 8. Re: Re: Re: Translating Excel formulas - IF AND

Unfortunately not

I coudln't find it in my available functions list, and I kept getting an error around parenthesis at character 90 (i.e at the 'IN'), so the formula wouldn't validate. I'm using Tableau 8 if that helps.

• ###### 9. Re: Translating Excel formulas - IF AND

Ok, I'm having a nother little issue with IF statements, which I can't resolve for love nor money!

The following code is only showing answers for options which relate to 'All Achieved'.

Any suggestions ...

if([BudgetOutcome]="Budget Incomplete" and([OrderOutcome]="Order Incomplete")) then "All Unachieved"

ElseIf([BudgetOutcome]="Budget Complete" and ([OrderOutcome]="Order Incomplete")) then "Budget Achieved"

ElseIf([BudgetOutcome]="Budget Complete" and ([OrderOutcome]="Order Complete")) then "All Achieved"

ElseIf([BudgetOutcome]="Budget Incomplete" and ([OrderOutcome]="Order Complete")) then "Order Achieved"

Else "" end

• ###### 10. Re: Re: Translating Excel formulas - IF AND

Hi Georgia,

Apologies for the really vague response to your op but have you tried using the raw_sql functions? - I haven't used these since Tableau 6 but they may allow you to query the workbook in the way that you want.

On your second question I had no issues though I did adapt the code to the following:

if [BudgetOutcome]="Budget Incomplete" and [OrderOutcome]="Order Incomplete" then "All Unachieved"

ElseIf [BudgetOutcome]="Budget Complete" and [OrderOutcome]="Order Incomplete" then "Budget Achieved"

ElseIf [BudgetOutcome]="Budget Complete" and [OrderOutcome]="Order Complete" then "All Achieved"

ElseIf [BudgetOutcome]="Budget Incomplete" and [OrderOutcome]="Order Complete" then "Order Achieved"

Else Null end

Edit: you could use string type or number in the final else, I just chose to use Null

• ###### 11. Re: Re: Translating Excel formulas - IF AND

Hi Steve,

I'm not sure what my issue is, but that code simply replaced my blanks with Nulls - which I expected.

Unfortuantely, I'm still experiencing the issue regardless of bracketing.

I haven't tried the raw-sql functions, I must look into these though. Thanks for the tip!

Cheers,

Georgia

• ###### 12. Re: Translating Excel formulas - IF AND

Hi Georgia,

I can't see any reason why the code should not work though can I make another couple of suggestions:

• Have you checked you source data to ensure the spelling and grammar are exactly as the calculation
• Try creating a new workbook against the data-source to check if the issue is something to do with the existing workbook (I've had this on a number of occassions)
• Try opening a new workbook an connecting to Superstore Sales then trying all of your calcs against this data - we all know this and you can then post additional issues using data that we can easily access and manipulate whilst you can try to achieve your intentions with a 100% stable data-source