1 of 1 people found this helpful
First, I'd like to welcome to to the Tableau Forums! You are definitely posting in the right place.
I am a bit unclear what you mean by "4-4-5 periods." If you can attach a packaged workbook (.twbx), showing what you have successfully accomplished, and the problems you are having, we can be of better assistance. Please see Tableau Forum Guidelines, Posting a Perfect Question |Tableau Support Community and Anonymize your Tableau Package Data for Sharing |Tableau Support Community for more information.
Thanks for the welcome Bill. I will anonymize the data now and upload, thanks for the links.
As suggested attached is the packaged workbook
4-4-5 is a representation of the number of weeks in a quarter.
The structure goes as follows, Year, Quarter, Period, Week
I have tried to show you how a typical year works. The start date therefore is not always the same but will be a Monday
Quarter Period Weeks 1 1 4 2 4 3 5 2 4 4 5 4 6 5 3 7 4 8 4 9 5 4 10 4 11 4 12 5 (Sometimes 6)
Hope this makes sense.
Fiscal Calendar Workbook.twbx 136.5 KB
Thank you. This is a great base on which to begin. Now, what exactly are you looking to produce? Can you be specific on the formula? Have you attempted it? If so, where did it go wrong?
I am looking to do the following, using 16/1 as an example date
Assuming the fiscal year starts on 29/6/15 and using the 4-4-5 method
17/01/16 is a Saturday,
1st of all I would like to compare 16/01/16 Saturday vs 17/1/2015 closest comparable Saturday from the previous year, this I have managed
2nd I would like to compare Period to date. 16/01/2016 falls into period 7 and it is the 20th day of this period.
I would like to compare 20 days of period 7 this year, vs 20 days of period 7 from last year, for the same relative days.
The same then needs to be done for Year to date,
I guess this could happen for all the calendar dimensions, week, period, quarter, half year and full year.
The reason for this is because the business is a very seasonal one with huge amounts of traffic at weekends so comparing January vs January in not a true reflection of business. One year may have 4 Saturdays and another 3 or 5.
I have tried a couple of approaches, I have tried creating Bins, calculated fields but I always back track.
Thanks for your time with this. I am currently recruiting for a tableau expert so fingers crossed we as a business can learn quickly.
If you are having data Date level ( MM-DD-YYYY), then it is easier to solve the situation using Date calculations.
we have to create several calculated fields to solve all of your queries.
1) Yesterday vs relative last year
sum(if datediff('day', [Order Date], today())= 1 then [Sales] end)
last year yesterdauy Sales
sum(if datediff('day', [Order Date], dateadd('year', -1, today() ) )= 1 then [Sales] end)
diffe of Yeaterday & Same day last year
[Yesterday Sales] - [last year yesterdauy Sales]
similarly we can create calculations, if you want to know list of Date functions available in tableau, go through the following link.
The following link gives idea about creating YTD Calculation.
The following link gives you clear idea on how to use the Date functions in Calculated fields.
how does this work with a custom year start date?
my years run
2015 = 30/6/2014 -> 28/6/2015
2016 = 29/6/2015 -> 26/6/2016