6 Replies Latest reply on Sep 24, 2014 6:35 AM by Karthick J

    Case Statement Error

    Sarah Gravely

      I'm trying to create a calculated field that will group multiple order dates.  This is the case statement I've created. I keep getting a syntax error and I'm not sure how to fix this. Please help!

       

      CASE

      WHEN[ORDDATE] BETWEEN #09/30/12# AND #10/27/12# THEN 'OCT_12'

      WHEN[ORDDATE] BETWEEN #10/28/12# AND #11/24/12# THEN 'NOV_12'

      WHEN[ORDDATE] BETWEEN #11/25/12# AND #12/29/12# THEN 'DEC_12'

      WHEN[ORDDATE] BETWEEN #12/30/12# AND #01/26/13# THEN 'JAN_13'

      WHEN[ORDDATE] BETWEEN #01/27/13# AND #02/23/13# THEN 'FEB_13'

      WHEN[ORDDATE] BETWEEN #02/23/13# AND #03/30/13# THEN 'MAR_13'

      WHEN[ORDDATE] BETWEEN #03/31/13# AND #04/27/13# THEN 'APR_13'

      WHEN[ORDDATE] BETWEEN #04/28/13# AND #05/25/13# THEN 'MAY_13'

      WHEN[ORDDATE] BETWEEN #05/26/13# AND #06/29/13# THEN 'JUN_13'

      WHEN[ORDDATE] BETWEEN #06/30/13# AND #08/03/13# THEN 'JUL_13'

      WHEN[ORDDATE] BETWEEN #08/04/13# AND #08/31/13# THEN 'AUG_13'

      WHEN[ORDDATE] BETWEEN #09/01/13# AND #10/05/13# THEN 'SEP_13'

      WHEN[ORDDATE] BETWEEN #10/06/13# AND #11/02/13# THEN 'OCT_13'

      ELSE 'ERROR'

      END

        • 1. Re: Case Statement Error
          kettan

          I think the formula uses two functionalities not supported in calculated fields in Tableau.

           

          CASE WHEN  (solution:  Use IF ... THEN ... ELSEIF ...)

          BETWEEN ... AND ...  (solution:  Use two conditions)

           

          This should work:

           

          IF [ORDDATE] >= #09/30/12# AND [ORDDATE] <= #10/27/12# THEN 'OCT_12'
          ELSEIF [ORDDATE] >= #10/28/12# AND [ORDDATE] <= #11/24/12# THEN 'NOV_12'
          ELSEIF [ORDDATE] >= #11/25/12# AND [ORDDATE] <= #12/29/12# THEN 'DEC_12'
          ELSEIF [ORDDATE] >= #12/30/12# AND [ORDDATE] <= #01/26/13# THEN 'JAN_13'
          ELSEIF [ORDDATE] >= #01/27/13# AND [ORDDATE] <= #02/23/13# THEN 'FEB_13'
          ELSEIF [ORDDATE] >= #02/23/13# AND [ORDDATE] <= #03/30/13# THEN 'MAR_13'
          ELSEIF [ORDDATE] >= #03/31/13# AND [ORDDATE] <= #04/27/13# THEN 'APR_13'
          ELSEIF [ORDDATE] >= #04/28/13# AND [ORDDATE] <= #05/25/13# THEN 'MAY_13'
          ELSEIF [ORDDATE] >= #05/26/13# AND [ORDDATE] <= #06/29/13# THEN 'JUN_13'
          ELSEIF [ORDDATE] >= #06/30/13# AND [ORDDATE] <= #08/03/13# THEN 'JUL_13'
          ELSEIF [ORDDATE] >= #08/04/13# AND [ORDDATE] <= #08/31/13# THEN 'AUG_13'
          ELSEIF [ORDDATE] >= #09/01/13# AND [ORDDATE] <= #10/05/13# THEN 'SEP_13'
          ELSEIF [ORDDATE] >= #10/06/13# AND [ORDDATE] <= #11/02/13# THEN 'OCT_13'
          ELSE 'ERROR'
          END
          
          
          

           

          Ps. Even if CASE WHEN is not supported, CASE value WHEN value THEN value is supported, such as:

           

          CASE [Region]
          WHEN 'East'  THEN 'East-South'
          WHEN 'South' THEN 'East-South'
          ELSE [Region]
          END
          
          • 2. Re: Case Statement Error
            Matt Lutton

            IF [ORDDATE] > #09/30/12# AND [ORDDATE] < #10/27/12# THEN 'OCT_12'

            ELSEIF...

             

            etc, etc.

            ELSE 'ERROR'

            END

             

            kettan, it seems I was posting this at the same time you were adding the IF conditions to your reply, I wasn't trying to steal your mojo!  When a good solution or competent help is provided, I try stay out of it!

            • 3. Re: Case Statement Error
              kettan

              Thanks. Corrected.

              • 4. Re: Case Statement Error
                Matt Lutton

                Not a problem    


                • 5. Re: Case Statement Error
                  Sarah Gravely

                  Thank you!

                  • 6. Re: Case Statement Error
                    Karthick J

                    Hi

                     

                    Is it possible to have 1 more Case statements in a single expressions. Or is there any suggestions.

                    -----------------------------------------------------

                    Case [What Iff] = "LV 1.1"

                    when  ([Meter Consumption]<=30) then [Meter Consumption]* 2.90

                    when ([Meter Consumption] > 30) and ([Meter Consumption] <= 50) then [Meter Consumption]* 3.40       

                    when  ([Meter Consumption] > 50) and ([Meter Consumption] <= 100) then [Meter Consumption]* 3.85

                    when  ([Meter Consumption] > 100) and ([Meter Consumption] <= 300) then [Meter Consumption]* 4.80

                    when ([Meter Consumption] > 300) and ([Meter Consumption] <= 500) then [Meter Consumption]* 5.20

                    when ([Meter Consumption] > 500)  then [Meter Consumption]* 5.50 else [Meter Consumption]* 5.20

                    end

                    --------------------------------------------------------

                    Case when  [What Iff] = "LV 2.1" and ([Meter Consumption] > 500)  then [Meter Consumption]* 5.50

                    end

                     

                    Bold text ( single case statement) is working fine. When i am not able to include the second case

                     

                    But my scenario is similarly i need to calculate it for LV 1.2 / LV 2.1 / LV 2.2) 3 more if case statement need to be with in the same expression.