2 Replies Latest reply on Jul 28, 2016 5:24 AM by Mark Fraser

    ElseIf Help

    Matt Peterson

      Looking for some help.   I have a transaction report that has two fields that are relevant to this discussion, Currency (USD/GBR/EUR/CAD/AUD) and Total (transaction amount in the declared currency).   I am trying to normalize this to USD and am using the following:

       

      IF [Currency]='GBP' THEN [Total]*1.31

      [ELSEIF] [Currency]='AUD' THEN [Total]*.75

      [ELSEIF] [Currency]='CAD' THEN [Total]*.76

      [ELSEIF] [Currency]='EUR' THEN [Total]*1.10

      [ELSE] [Total] END

       

      I keep getting the error

      Expected "END" to match "IF" at character 0

       

      Any ideas where the syntax is incorrect?   I've seen a number of these on the forum, but I didn't see any that included a calculation referencing another cell.

       

      Thanks,

        • 1. Re: ElseIf Help
          Peter Halpin

          Hi,

           

          The issue is probably arising due to some unneeded square brackets in the calculation (square brackets are not needed to enclose ELSEIF or ELSE statements). Try using this calculation:

           

          IF [Currency]='GBP' THEN [Total]*1.31

          ELSEIF [Currency]='AUD' THEN [Total]*.75

          ELSEIF [Currency]='CAD' THEN [Total]*.76

          ELSEIF [Currency]='EUR' THEN [Total]*1.10

          ELSE [Total] END

           

          Hopefully this helps!

          1 of 1 people found this helpful
          • 2. Re: ElseIf Help
            Mark Fraser

            Matt

            IF [Currency]='GBP' THEN [Total]*1.31

            [ELSEIF] [Currency]='AUD' THEN [Total]*.75

            [ELSEIF] [Currency]='CAD' THEN [Total]*.76

            [ELSEIF] [Currency]='EUR' THEN [Total]*1.10

            [ELSE] [Total] END

             

            Tableau thinks things in square brackets [ ] are existing measures, parameters or something... as Peter rightly suggests, you don't need them.

             

            Peter's formula should work, except you may need to wrap SUM() around the total (depending on the data/ aggregation)

            So...

            IF [Currency]='GBP' THEN SUM([Total])*1.31

            ELSEIF [Currency]='AUD' THEN SUM([Total])*.75

            ELSEIF [Currency]='CAD' THEN SUM([Total])*.76

            ELSEIF [Currency]='EUR' THEN SUM([Total])*1.10

            ELSE SUM([Total]) END

             

            Cheers

            Mark