8 Replies Latest reply on Feb 5, 2019 7:02 PM by Prasanna R

# What Am I doing Wrong?

Hello guys

I have couple of excel formulas which i am trying to replicate in Tableau

Excel:  = IF(A1=F,0,IF(AND(B1<>'E',C1<>'X'),1,0))

Tableau:

if A1='F' then 0

elseif (B1<>'E' and C1<>'X') then 1 else 0 end

the tableau calculation is valid but i am getting wrong values.

Any insight is appreciated

Regards

J

• ###### 1. Re: What Am I doing Wrong?

Hi Johnny,

Can you try this.

IF A1='F'

THEN 0

ELSE

IF (B1<>'E')  AND (C1<>'X')

THEN 1

ELSE 0

END

END

Best Regards

RP

• ###### 2. Re: What Am I doing Wrong?

Thanks Prasanna for taking the time

but i am just getting 0 when i am using your calculation as i was getting for mine.

Regards

J

• ###### 3. Re: What Am I doing Wrong?

Hi, could you attach a packaged workbook with some sample data?

• ###### 4. Re: What Am I doing Wrong?

Hi Johnny,

Per the formula you had given it will result in 1 only in one case as shown below. Rest all will have 0s.

=IF(A2="F",0,IF(AND(B2<>"E",C2<>"X"),1,0))

Using the calculation I had posted the results in Tableau is as shown below:

Best Regards

RP

• ###### 5. Re: What Am I doing Wrong?

Hi Johnny,

Your calculation looks like it should work as you're intending.  What do your results look like and what do you want them to look like?

Best,

Paul

• ###### 6. Re: What Am I doing Wrong?

Hello Prasanna

Thank you for taking time and giving such a detailed reply. but i am just getting 0 when i use your approach. i was able to achieve it by using 2 calculations.

Cal1)

IF A1='False' THEN 0

ELSE

IIF(B1<>'E' AND C1<>'X',1,0) END

for the above calculation i was getting Null and 0 so i wrote another calculation to replace Null with 1. But my values are matching perfectly.

Cal2)

IFNULL(Cal1,1)

still thanks a lot. there are few which i am unable to crack you can have a look at them if you have some time.

Excel-1)    =IF(OR(LEFT(A1,1)*1=5,B1='AMERICA',C1=FALSE,AND(D1='AR',E1='Z')),0,IF(OR(F1='',TEXT(F1,0)="0"),0,IF(G1*1<>F1*1,1,0)))

Excel-2)    =IF(A1=F,0,IF(OR(AND(B1='Z',TEXT(C1,0)<>'47'),AND(TEXT(D1,0)="99",TEXT(C1,0)<>"47")),1,0))

Excel-3)    =IF(OR(A1=F,B1<>"Z",C1<>"11X",D1<>"Q",E1="" AND(LEFT(F1,6)<>"AMERICA",LEFT(F1,6)<>"LAGPP")),0,"EXE")

Excel-4)     =IF(OR(A1=F,B1<>"Z",C1<>"11X",D1<>"Q",E1="",AND(LEFT(F1,6<>"AMERICA",LEFT(F1,6<>"LAGPP")),0,IF(AND(G1<>'14',G1<>'77',G1<>'89'),1,0))

Excel-5)     = IF(A1=F,0,IF(AND(M1<>'88',M1<>"22",M1<>"K77"),0,IF(TEXT(H1,0)<>"47",1,0)))

i am able to create a valid calculation but the number of records are not getting matched with the  excel sheet.(number of 0 records and Number of 1 records)

sorry i am unable to attach a sample workbook as it contains many fields and i am just unable to replicate these logic's.

Thank a Ton

Regards

J

• ###### 7. Re: What Am I doing Wrong?

Hey Paul Thank You for replying

Yes even i am confused but i got it rectified by using below 2 calculation

Cal1)

IF A1='False' THEN 0

ELSE

IIF(B1<>'E' AND C1<>'X',1,0) END

for the above calculation i was getting Null and 0 so i wrote another calculation to replace Null with 1. But my values are matching perfectly.

Cal2)

IFNULL(Cal1,1)

if you can provide any insights for the below !!

Excel-1)    =IF(OR(LEFT(A1,1)*1=5,B1='AMERICA',C1=FALSE,AND(D1='AR',E1='Z')),0,IF(OR(F1='',TEXT(F1,0)="0"),0,IF(G1*1<>F1*1,1,0)))

Excel-2)    =IF(A1=F,0,IF(OR(AND(B1='Z',TEXT(C1,0)<>'47'),AND(TEXT(D1,0)="99",TEXT(C1,0)<>"47")),1,0))

Excel-3)    =IF(OR(A1=F,B1<>"Z",C1<>"11X",D1<>"Q",E1="" AND(LEFT(F1,6)<>"AMERICA",LEFT(F1,6)<>"LAGPP")),0,"EXE")

Excel-4)     =IF(OR(A1=F,B1<>"Z",C1<>"11X",D1<>"Q",E1="",AND(LEFT(F1,6<>"AMERICA",LEFT(F1,6<>"LAGPP")),0,IF(AND(G1<>'14',G1<>'77',G1<>'89'),1,0))

Excel-5)     = IF(A1=F,0,IF(AND(M1<>'88',M1<>"22",M1<>"K77"),0,IF(TEXT(H1,0)<>"47",1,0)))

i am able to create a valid calculation but the number of records are not getting matched with the  excel sheet.(number of 0 records and Number of 1 records)

Thank a Ton

Regards

J

• ###### 8. Re: What Am I doing Wrong?

Hey J,

Please attach the sample data in excel so that I can help you with the Tableau calculation for the same.

BR

RP