4 Replies Latest reply on Oct 18, 2018 12:04 AM by Mark Fraser

    Help required for Case Statement in Calculated field

    Tracy McLean

      Hi

       

      I have re-engineered a Case statement in my workbook and do not get any results, if apply only one of these statements they don't work by themselves, however when I take my 'If' statement and trial this in it's own calculated field it works.

       

      CASE [TIME PERIOD]

          WHEN "YTD" then if [Reporting Dt] = [YTD DATE RANGE] THEN 1 ELSE 0 END

          WHEN "MTD" THEN IF [Reporting Dt] = [MTD DATE RANGE] THEN 1 ELSE 0 END

          WHEN "WTD" THEN IF [Reporting Dt] = [WTD DATE RANGE]  THEN 1 ELSE 0 END

          WHEN "LY" THEN IF [Reporting Dt] = [LAST YEAR DATE RANGE]  THEN 1 ELSE 0 END

          WHEN "LM" THEN IF [Reporting Dt] = [LAST MONTH DATE RANGE]  THEN 1 ELSE 0 END

          WHEN "LW" THEN IF [Reporting Dt] = [LAST WEEK DATE RANGE ]  THEN 1 ELSE 0 END

      END

       

      Anyone got any ideas on how to rectify this.  Wanting to use this calculated field as a filter.

       

      Fingers crossed someone can help

        • 1. Re: Help required for Case Statement in Calculated field
          Mark Fraser

          Hi Tracy

           

          Certainly, that 'type of' syntax should work, i just tried an example in the superstore workbook

           

          CASE [Sub-Category]

          WHEN 'Art' THEN IF [Product Name] = 'American Pencil' THEN 1 ELSE NULL END

          END

           

          To check - The output of [TIME PERIOD] is YTD, MTD etc.?

          And what are the types of [Reporting Dt] and [YTD DATE RANGE] are they both dates? numbers? something else? if they dont match, it wont work

           

          More examples are useful!

           

          Cheers

          Mark

          • 2. Re: Help required for Case Statement in Calculated field
            seraj alam

            can you please attached sample workbook for the same.

            • 3. Re: Help required for Case Statement in Calculated field
              Tracy McLean

              Thanks Mark

               

              My parameter is set up as

               

              The Reporting Date and MTD Date Range are both date dimensions

               

              Result I get is below

              Hopefully something simple that I have missed doing to get out the result I need.

               

              Cheers

              Tracy

              • 4. Re: Help required for Case Statement in Calculated field
                Mark Fraser

                Hi Tracy

                 

                Thanks for the extra information!

                Based on the parameter screenshot, then i think the bit in bold should work - you're testing a string against a string

                 

                CASE [TIME PERIOD]

                    WHEN "YTD" then if [Reporting Dt] = [YTD DATE RANGE] THEN 1 ELSE 0 END

                END

                 

                the other part is the date test, highlighted bold below

                 

                CASE [TIME PERIOD]

                    WHEN "YTD" then if [Reporting Dt] = [YTD DATE RANGE] THEN 1 ELSE 0 END

                END

                 

                I can see from your screenshot that [Reporting Dt] doesn't include time so i assume (and its good to check) that the data is DD-MON-YY, it could be DD-MM-YY (or with YYYY)

                I can also see [MTD DATE RANGE] is a calculated field (i assume same for [YTD DATE RANGE] etc.) it would be good to check the format of this field - it should match the [Reporting DT] or else you should align them with DATE (Date Functions ) functions - such as TRUNC

                Once we have at least ensured that the format of the items align - we can be sure its something else...

                 

                overall advice

                if possible - try to replicate a smaller data set, smaller function etc. it maybe easier to see the behavior, at the moment you have 6 clauses, try with just 1

                and further, as you're using a logic test (IF) you need to ensure that you have results which will match TRUE...

                 

                From the workbook (and your use of parameters, YTD etc.) i assume you either have some tableau background, or you have inherited this workbook from someone else - certainly this is more 'advanced'

                finally, i wrote this sometime back, maybe helpful in some small way >> Time periods in Tableau

                 

                Certainly with the communities help, we'll get there!

                 

                Cheers

                Mark