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.
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;
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!
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
and then you'll need to convert it back to a string to concatenate it to your date string you're building;
You beat me to it, I used a different approach, I just nested a 'Right' statement with what you wrote:
Thanks very much for all of your help!