
1. Re: Compare Year over Year Quarterly results
Jennifer VonHagel Dec 6, 2018 6:00 PM (in response to Paddy Kavanagh)Hi Paddy, here are a few options.
Option 1: Turn your Quarter into a date in Tableau, and create a calculation to find the latest Quarter in your data and the corresponding prior year quarter.
1. Change the datatype of Quarter from String to Date  Tableau will take care of the rest. In the left pane, find the Quarter field, click the "Abc" data type on the left and choose "Date".
2. Create a calculation to call out the time periods you want to see:
In the following calculation, the { FIXED : MAX([Quarter Date]) } calculation allows you to reference the MAX Quarter across the entire data set, even on rows where the quarter is not the MAX quarter. So we can check your row of Quarters and test whether each quarter is equal to the { FIXED : MAX(Quarter) } and label it accordingly. The second part of the calculation uses the DATEADD() function to find the date one year prior to the { FIXED : MAX([Quarter Date]) }.
Time Periods (Dt):
IF [Quarter Date] = { FIXED : MAX([Quarter Date]) }
THEN 'Latest Quarter'
ELSEIF [Quarter Date] = DATEADD('year',1,{ FIXED : MAX([Quarter Date]) })
THEN 'Prior Year'
ELSE 'Exclude'
END
You can put Time Periods (Dt) on the Filter and exclude the "Exclude" values, leaving only the two dates you care about in the view:
Option 2: If for some reason you need to leave your Quarter as a string, you can use string manipulation functions to find the latest Quarter in your data and the corresponding prior year quarter. Because your quarters are in format Year QQ, looking for the max(Year QQ) will yield the latest one.
In the following calculation, the { FIXED : max(Quarter) } calculation allows you to reference the MAX Quarter across the entire data set, even on rows where the quarter is not the MAX quarter. So we can check your row of Quarters and test whether each quarter is equal to the { FIXED : MAX(Quarter) } and label it accordingly. The second part of the calculation tests whether the first 4 characters (year) of each quarter is = the first four characters of the { FIXED : MAX(Quarter) }'s year minus one (last year), AND checks that the right most character (1,2,3, or 4) is equal, as we want the same quarter of the prior year.
Time Periods:
IF [Quarter] = { FIXED : MAX([Quarter]) }
THEN 'Latest Quarter'
ELSEIF
//Year of Quarter = Year of MAX Quarter  1
INT(LEFT([Quarter],4)) = INT(LEFT({ FIXED : MAX([Quarter]) },4))  1 AND
//Quarter of Quarter = Quarter of MAX Quarter
RIGHT([Quarter],1) = RIGHT({ FIXED : MAX([Quarter]) },1)
THEN 'Prior Year'
ELSE 'Exclude'
END
Workbook is attached.
Best,
Jennifer

YoY Quarters.twbx 23.4 KB


2. Re: Compare Year over Year Quarterly results
Paddy Kavanagh Dec 7, 2018 3:51 AM (in response to Jennifer VonHagel)This is awesome Jennifer  Thank you!! This has answered my question
Because I'm dealing with Fiscal Quarters I'll stick with the Option 2
With this method, is it possible to pick out the 'Prior Quarter' 
The reason for this is, after looking at your explanation of how it works, I'm thinking of creating a parameter so the dashboard user has an option to compare quarters that are either Q/Q or Y/Y
Thank you again!

3. Re: Compare Year over Year Quarterly results
Jennifer VonHagel Dec 7, 2018 9:51 AM (in response to Paddy Kavanagh)Hi Paddy, sure:
Since we'll be referencing the Latest Quarter all the time, I'm going to go ahead and make it it's own calculation to make writing other calcs a little simpler:
I've added Prior Quarter into the Time Periods (Str) IF statement.
To translate to English, if the Latest Quarter's quarter is Q1, then we want to find the quarter with Latest Quarter's year1 and necessarily Q4. If the latest quarter is not Q1, we can use the same year as Latest Quarter and find Latest Quarter's Qx  1.
IF [Quarter] = [Latest Quarter]
THEN 'Latest Quarter'
ELSEIF //Get same quarter last year
//Year of Quarter = Year of MAX Quarter  1
INT(LEFT([Quarter],4)) = INT(LEFT([Latest Quarter],4))  1 AND
//Quarter of Quarter = Quarter of MAX Quarter
RIGHT([Quarter],1) = RIGHT([Latest Quarter],1)
THEN 'Prior Year'
ELSEIF //Get prior quarter
//Prior Qtr YEAR: If Q1, find prior year, else find same year
INT(LEFT([Quarter],4)) =
IF INT(RIGHT([Quarter],1)) = 1
THEN INT(LEFT([Latest Quarter],4))  1
ELSE INT(LEFT([Latest Quarter],4))
END
AND
//Prior Qtr QUARTER: IF Q1, look for Q4 else look for Qx  1
INT(RIGHT([Quarter],1)) =
IF INT(RIGHT([Latest Quarter],1)) = 1
THEN 4
ELSE INT(RIGHT([Latest Quarter],1))  1
END
THEN 'Prior Quarter'
ELSE 'Exclude'
END
Best,
Jennifer

YoY Quarters.twbx 23.6 KB
