3 Replies Latest reply on Apr 22, 2015 3:04 PM by Carlos Flores

    Quick filter or parameter? Neither are solving my problem

    jonathan.sabo

      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!

        • 1. Re: Quick filter or parameter? Neither are solving my problem
          Carlos Flores

          Sounds like you want.

           

          User picks: 2005

          Options available: 2004,2003,2002

           

          User picks 2006

          Options available: 2005,2004,2003

           

          and so on, somewhat of a conditional parameter, which we still dont have... but you we do have workarounds.

           

          Few things to explore.

           

          Using ranking, if you can use years as the ranking, you can tie the data so that when you pick a year, it will show the top 3 rankings, which would be the 3 years before.

           

          This approach might be worth exploring.

           

          Other option is 2 parameters but you have the limitations you list, all years would be available on each parameter.

           

          Parameter 1 : Year

          Quickfilter:: All years

          • 2. Re: Quick filter or parameter? Neither are solving my problem
            jonathan.sabo

            I think we're on the same page as to what I'm looking for.

             

            Step 1: User Chooses one of the following years: User selects "2015"

            • 2013
            • 2014
            • 2015

            Step 2: User Selects Current Forecast which is cascaded based on Year selection.  Forecasts from 2014 and 2013 would not be visible.  User sees the following and selects "2015: March". (Note that at the beginning of May, the list must automatically expand to include "2015: May" as an option when the data set is refreshed.)

            • 2015: Jan
            • 2015: Feb
            • 2015: March
            • 2015: April

            Step 3: User Selects Prior Forecast (but is the same field/column as step 2) which is cascaded based on Step 1 and Step 2 selection.  User sees the following and selects "2015: Jan". (Note that March and April are not visible to the user since they are not prior to step 2. Prior forecast must be in the same fiscal year)

            • 2015: Jan
            • 2015: Feb

             

            Step 4: Analysis - Compare "2015: March Results" to "2015: January Results". It is critical that neither step 2 or step 3 filter out March and January.  However, when I filter on March in step 2, all other months are now excluded.

             

            Right now I'm stuck at step 3.  There are some additional complications once that is achieved, but I'm assuming a solution to step 3 will likely solve the other complications I am foreseeing.

             


            • 3. Re: Quick filter or parameter? Neither are solving my problem
              Carlos Flores

              Sounds like you will need to work on the logic in your source or through Tableau.

               

              http://www.theinformationlab.co.uk/2014/06/19/popping-parameters/

               

              This concept should work, but you will need to set up some calculated fields to know the previous months.