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

    What Am I doing Wrong?

    Johnny K

      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?
          Prasanna R

          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

           

          *If this helps, then please mark it Helpful and Correct to close this thread

          • 2. Re: What Am I doing Wrong?
            Johnny K

            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?
              ShivaRam Chennapragada

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

              • 4. Re: What Am I doing Wrong?
                Prasanna R

                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

                 

                *If this helps, then please mark it Helpful and Correct to close this thread

                • 5. Re: What Am I doing Wrong?
                  Paul Wachtler

                  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?
                    Johnny K

                    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?
                      Johnny K

                      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?
                        Prasanna R

                        Hey J,

                         

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

                         

                        BR

                        RP