5 Replies Latest reply on Jul 20, 2016 8:41 AM by Peter Seok

    Formula Strings Question

    Peter Seok

      Hi Everyone,

       

      Just a quick question on syntax in Tableau, I've tried a bunch of things, and I can't seem to get my formula to work properly.  I have a field that denotes a particular quarter of the year, and it's always in this particular format:

       

      2015/2016 Q1

      2016/2017 Q3

      2016/2017 Q4

      2015/2016 Q2

       

      However, my fiscal year starts on 4/1, so the quarters are actually shifted forward by 3 months, so I've listed the actual quarter end dates for the 4 examples above:

       

      2015/2016 Q1 is actually 6/30/2015

      2016/2017 Q3 is actually 12/31/2016

      2016/2017 Q4 is actually 3/31/2017

      2015/2016 Q2 is actually 9/30/2015

       

      I just want to convert the string to a date, so I wrote the following formula:

       

      IF RIGHT([Report Date]) = "Q1" THEN "6/30/" + LEFT(4) ELSEIF IF RIGHT([Report Date]) = "Q2" THEN "9/30/" + LEFT(4) ELSEIF IF RIGHT([Report Date]) = "Q3" THEN "12/31/" + LEFT(4) ELSEIF IF RIGHT([Report Date]) = "Q4" THEN "3/31/" + LEFT(4)+1 END

       

      I'm not sure what the operator to just combine strings is, so I'm currently using a '+', but & doesn't seem to work as it does in Excel.  Any help is greatly appreciated, thanks very much!

        • 1. Re: Formula Strings Question
          Tom W

          So, you have a few problems;

          • You're using the RIGHT function but not specifying how many characters to take from the right
          • The syntax for the if function is IF something THEN dosomething ELSEIF somethingelse THEN dosomethingelse ELSE catchalldosomethingelse END
          • You're using the LEFT function as LEFT(4). Similar to the RIGHT function, you need to take the LEFT of something for X characters.
          • 2. Re: Formula Strings Question
            Tom W

            Further follow up;

            You will benefit a lot from formatting things using linebreaks in Tableau. You aren't confined to the same formula bar as excel;

            I.e. look at how readable this is versus yours!

            There's also no need to repeat yourself with that LEFT function. You can use an IF and then combine it with something else, like another IF. I.e;

             

            • 3. Re: Formula Strings Question
              Peter Seok

              Hi Tom,

               

              Thanks so much for coming back so quickly, the formulas both work perfectly, just one last question...for anything that's falling in Q4, the year won't be the first 4 characters of the string, it'll be the next year after that.  So for a 2015/2016 Q4, the date should actually read 3/31/2016.

               

              I tried adding a +1 to left([Report Date],4), but it looks like it won't allow me to add an integer to a string value.  Do you know how I'd get around this?  It's only in the event of a "Q4", so I don't think the formula should need much more, thanks again!

              • 4. Re: Formula Strings Question
                Tom W

                LEFT([Report Date],4) will return a string - "2015". You would need to convert that to an integer first - INT(LEFT([Report Date],4)) + 1

                 

                EDIT;

                and then you'll need to convert it back to a string to concatenate it to your date string you're building;

                STR(INT(LEFT([Report Date],4))+1)

                • 5. Re: Formula Strings Question
                  Peter Seok

                  Hi Tom,

                   

                  You beat me to it, I used a different approach, I just nested a 'Right' statement with what you wrote:

                   

                  Right(Left([Report Date]),9),4)

                   

                  Thanks very much for all of your help!