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

# Formula Strings Question

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

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

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

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

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

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!