One way to accomplish this would be to have a separate sheet for the YoY % difference and then combine the two views onto the dashboard... I know the title of your post says you want it 'in one worksheet' but the end user will not know the difference?
Another solution without the combining on a dashboard workaround would be to duplicate your datasource and create a calculated field in there (to get around the filter on the primary source), or you may be able to use some pass thru (RAWSQL) functions instead.
example_for_forum.twbx.zip 950.9 KB
I started playing with this and came up with another solution. I'm not sure which is easier / better in your particular case, but you might find it helpful.
4. Year on year (to date) % change for those same measures
It's the fourth thing I can't accomplish, as I've needed to use a date filter to existing FY only to satisfy the other criteria
You're right that the filter prevents you from doing the YoY calculation, since the filter is applied at the data source, before Tableau's visual engine and any of your calculations. My approach was to return both years, using a YTD filter.
The fiscal year (I think that's what you mean by FY above?) made this a little tricky. As you probably know, when you make a date field a fiscal date with the format option, you're just changing how Tableau displays the date. Calculations that use that date ignore the FY formatting.
I created a fiscal year function that mimics Tableau's FY display. This is based on Jonathan Drummey's calc that you can read more about here: Year to Date (YTD) from Previous Year and Fiscal Year..
For example, if your fiscal year starts on July, then June 30, 2013 is FY2013 and July 1 is FY2014.
Order Date Fiscal Year =
IF MONTH([Fiscal Year Start]) == 1 // In the case of January, FY = current year
THEN YEAR([Order Date]) // ELSE FY +=1 if current month > fiscal year start month.
ELSE YEAR([Order Date]) + IF MONTH([Order Date]) >= MONTH([Fiscal Year Start]) THEN 1 ELSE 0 END
After adding this, I edited the format (right-click > Default properties > Number format > Number custom) to eliminate the "," separator and add the "FY" prefix.
I duplicated this calc to create Current Fiscal Year, replacing [Order Date] with [Current Date]. After testing, you'd want to replace references to [Current Date] with TODAY().
Then I use this in the Order date YTD Filter. There are additional comments int the attached workbook and again I used a slight variation of Drummey's formula that allows for a January FY start month. For example, if the fiscal month start is October and the current date is Dec 31, 2013, then you want dates between Oct 1, 2013 and Dec 31, 2013 and Oct 1, 2012 and Dec 31, 2012 for the previous FY.
Order Date YTD for last 2 years FY
// Order Date > DATE("2012-10-01") AND Order Date < 2013-10-31.
([Order Date] >= DATE(STR([Current Fiscal Year]-IIF(MONTH([Fiscal Year Start]) == 1,0,1)) + "-"+STR(MONTH([Fiscal Year Start]))+"-01")
AND [Order Date] <= [Current Date])
OR // Previous FY YTD: Order date > DATE("2011-10-01") AND Order Date < 2012-12-31
([Order Date] >= DATE(STR([Current Fiscal Year]-IIF(MONTH([Fiscal Year Start]) == 1,1,2)) + "-"+STR(MONTH([Fiscal Year Start]))+"-01")
AND [Order Date] <= DATEADD('year',-1,[Current Date]))
You also need to create parameters for Current Date---which you could replace with TODAY() after testing---and Fiscal Year Start.
After doing this you can
- replace your date filter with this field
- replace Year in the columns shelf with Order Date Fiscal Year
- add the percent difference table calcs (I used quick table calcs) and get a view similar to "All Fields".
Hiding the previous Year
You can just right-click on the FY2013 and click hide. But FY2013 will always be hidden, which will cause a problem; for example, if you change the FY start month to January the last two FYs are 2012 and 2013 and 2013 is still hidden. There's a cute workaround to this called a table calc filter, which relies on the fact that filters based on table calcs are applied after all of the data is returned. You create a table calc that duplicates your dimension, in this case Order Date Fiscal Year.
Order Date Fiscal Year (hide)
LOOKUP(MIN([Order Date Fiscal Year]), 0)
Then I created a calculated field that's TRUE when Order Date Fiscal Year == Current fiscal year.
Hide previous FY
[Order Date Fiscal Year (hide)] == [Current Fiscal Year]
Move Order Date Fiscal Year to the detail shelf, and put Order Date Fiscal Year (hide) to the columns shelf.
Add Hide previous FY to the filters.
If I glossed over a few steps above, let me know. Oh and I didn't thoroughly test this, so ...
example_for_forum_jimw_v2.twbx.zip 987.4 KB
Nice solution, Jim!
Had the idea of using the LOOKUP table calc trick at the back of my mind but couldn't quite get it to work without using YEAR in the view... I see that's moot anyway because you can just hide it.
I cleaned up the workbook (attached as _V2 above) and edited the description, after finding the below link in the table calc library (would have saved some time if I had looked there first or @Jonathan Drummey's Wiki ...).
Tableau Calc Reference Library
The specified item was not found.
YTD FY Calcs
I also added a new view to test the YTD FY filter.
Jim & Robin - a thousand thanks, you guys are amazing
I will test this today. I will also add the wiki and table calc library you speak of to my list of resources. Tried to self serve but could not find anything using google!