I am trying to use Tableau to replicate monthly Sales forecast reports. My dataset consists of several Excel snapshots (one per month) going back several years. The snapshots all have identical columns, so it was easy to stack them on top of each other into one large data set. When the data set was formed, one additional column was added to note which forecast (referred to as "Forecast Period" the data was from (examples below:)
2014: Forecast 1
2014: Forecast 2
2014: Forecast 12
2015: Forecast 1
2015: Forecast 2
2015: Forecast 3
2015: Forecast 4
I set up a parameter with a list of Years, and then created a calculated field for the "Forecast Period" comparing the first 4 characters to the year selected in the year parameter. If there was a match, then I kept the "forecast period", if not, null. I then created a set off of this new calculated field that excludes the nulls. I then add the set to the filter. This all allows me to bring the "forecast period" into a quick filter and only see the relevant values for the year selected (this limits the quick filter to a maximum of 12 values based on the year selected, rather than showing a list of every forecast in the data set).
The process above allows me to successfully select what I call my "Current Forecast" from a list of 12 values based on the year parameter. My next step is to select a "Prior Forecast" for comparison (I always have a Current and a Prior to Compare).
Example: User Selects "2015" in Year Parameter. They now have 4 options (since I only have 4 forecasts YTD in 2015) to choose for the "Forecast Period". The user selects "2015: Forecast 3", and this is considered the "Current Forecast". The next step is to have the user select a "Prior Forecast". I want the user to see all Forecasts in the current year prior to the Current Forecast. In this example, I would like the user to only have the option to choose "2015: Forecast 1" or "2015: Forecast 2". The issue is, by using a filter to select the Current Forecast, I have eliminated all of the options to choose for the Prior Forecast. I could select the Current Forecast using a Parameter rather than a filter, but then I lose the cascading ability based on the Year Parameter selection. It also makes my lists of Current Forecasts static in the parameter, which would mean intervening every month when a new forecast is added to change the Parameter options.
I was able to tie the numbers out and make my analysis kind of work using only Parameters and no quick filters. There are several problems with this though:
1) The lists for the user to choose from is very long since there is no cascading
2) The lists do not dynamically update with a new selection when new data is added
3) The lists do not prohibit the user from selecting a Prior Forecast that is after the Current Forecast since there is no cascading
Because the use of Parameters seems to be unusable for the reasons above, that leaves me with filtering as my only option. The biggest challenge is, how can I filter to two options of the same field, with the first selection limiting* the options of the second selection (but not eliminating all options as they are now).
*Basically allowing the user to select one option from the field, and then Tableau says "based on what you selected in that field, we will allow you to select one more item of the same field, but with additional limitations on what is available to be selected"
Pretty long post, please let me know if anyone needs additional details. Thanks!