Finding Comparison Periods  Can my formula be improved
Simon Runc Nov 13, 2015 6:25 AMHi All,
...So following on from an unrelated post Using calculations 'on the left'  LoD I posted an example LoD I use to work out the first week of data I have, from the previous year...so that any YoY comparisons only include comparative weeks. Although it works...the calc is 'ugly' (not just aesthetically, but also it's not the most efficient) and am reaching out to anyone who can improve up on it. Rody Zakovich seemed up for the challenge!..as always!
The situation we use this; When we get a new client, we often don't have more than 60ish weeks of data (we scrape data and the system we scrape only goes back 60 weeks). However we still want them to be able to use our YtD Comparison dashboard. So as well as knowing the last week of data, we also need to calculate the first week of data (from the previous year) so we only compare 'comparative' weeks..suitably confused!...
In the attached you'll see my current formula for this (there is also a similar one for picking the last week, but ignore that for now...that get's written into the data by our excellent DBA)
[First Week Prev Year]
INT(RIGHT(str({MIN(IIF([Year] = {MAX([Year])}1,INT(str([YearWeek Int])),NULL))}),2))
The below image (which is taken from the 'What's it doing' tab in the attached  where I've broken out the elements, using Tableau's very useful 'grab a bit of a calc field and drag it to the Viz'!). Here's a quick description;
[Year] = {MAX([Year])}1 //Gets the Previous Year
{MIN(IIF([Year] = {MAX([Year])}1,INT(str([YearWeek Int])),NULL))} //For Previous Year get the MIN of the YEARWEEK
Finally Get the Right Two digits and turn them into an Int
What I like about this formula, and (ideally) needs to be maintained in any solution, is that results are returned at Row Level. This means I can use any VizLoD I want, and as you can see I've then created a 'calculated dimension' to apply
[Time Period Class]
IF [Week No Int] <= [First Week Prev Year] THEN 'No Comparison'
ELSEIF [Week No Int] > [Last Week  Current Year] THEN 'Prev Year to Year End'
ELSE 'Comparison'
END
I've also included a 'slimmed' down version of our final dashboard so you can see how we use it.
I wrote this formula pretty quickly and was a 'pragmatic' solution at the time, so if anyone can think of a different way of accomplishing the same goal...it will be very much appreciated.
Attached Workbook in T9.0

First Week Prev Year Calc.twbx 52.7 KB