1 of 1 people found this helpful
What is the length of your periods? months?
One way to accomplish what you're after would be to create a calculated field that will go ahead and create a starting date for each period. (You'd of course want to include future periods as well) Then, you would be able to use relative dates.
If your period field is formatted like 2012-01, 2012-02, 2012-03, then you could make a "date converter" field something like:
DATEADD('month',INT(RIGHT([Period],2)), DATEADD('year',INT(LEFT([Period],4)),#1/1/1900#) )
// gives date value Y /1/XXXX with correct year X and month Y
DATEADD('year',INT(LEFT([Period],4)),#1/1/1900#) //gives date value 1/1/XXXX with correct year
Then, you can filter this created Date field with Tableau's relative date filters...
To add to the original request - the fiscal year crosses calendar years (currently in fiscal year 2013) and the periods are 01 thru 13 and are not related to the months. We would like to default to the max but allow the user to select a previous period and/or fiscal year.
Thanks for the info Mark. We added the 'end date' of each period to the datasource, so now we have a date we can use in our relative filter and can default it to the Current or Prior period.
Once it is defaulted using the relative date filter, how can we have the end users then user the Period filter to change fiscal periods without having to update the relative filter?
The relative filter only gives us the options of changing calendar based fields (months/weeks/days, etc). As BJ mentioned, we have 13 fiscal periods, each at 4 weeks.
ex: we default it to fiscal period 6 by using the 'last 4 weeks' relative filter, and someone wants to change it to 1, how can they do that without explicitly updating the relative filter to "last 24 weeks"?
Can you post your workbook?
There are a few ways you could attack this. In general, I think you could either figure out the way to use the relative filter as desired, or to create a parameter + calculated field combination that would enable you to give the user choices and then set the view up to filter differently for each selection.
From what you described most recently, it almost sounds like you are saying you want to enable the user to pick discrete fiscal periods i.e., Period 6 (11/18/12 to 12/16/12) or Period 2 (7/29/12 to 8/26/12), which I think is what you started with, so maybe I need to see it to understand what issue you're facing...
Attached is an example. We have added the Fiscal Period end date to each row (similar to joining the fiscal period table to our transaction table).
The requirement is to allow the user to look at any fiscal period or combination of periods they want, however when they first login, it should default to the current period (also in our case will be the max fiscal period/year in our table).
So if I log in, my initial view should be set to the current fiscal period, but allow me to change to whatever I want.
FiscalYearTest.twbx.zip 19.8 KB
Does the attached accomplish what you're after?
"Current Period" will change based on the date of today. Today 11/21 fits into period 7, so that is the one that "becomes" the current period until 12/15. Then, period 8 will become "Current Period" and 7 will revert to "Period 7."
If you like, I put the Current Period "definition" in the view title so it's obvious.
Also, if you prefer to chop off the dates from the other periods, you can edit the [Filter] field and use the line that is commented out instead.
FiscalYearTest.twbx.zip 20.6 KB
You're the man Mark. Thanks!