7 Replies Latest reply on Nov 21, 2012 12:16 PM by Cote Adams

    Default Filter Selection

    Cote Adams

      Is it possible to default the filter selection based on the source data?

       

      I have a live connection to a SQL database, and my requirements for one of the dashboards is to default the current fiscal period and year (in our case the max period and max year in the database) leaving the option for the end user to change this filter if needed.

       

      I can't use relative date as we don't store the calendar date or conversion to fiscal calendar in this database. We simply want to default the filter selection to the max period.

       

      Thanks.

        • 1. Re: Default Filter Selection
          Mark Holtz

          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...

          1 of 1 people found this helpful
          • 2. Re: Default Filter Selection
            BJ McGovern

            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.

            • 3. Re: Default Filter Selection
              Cote Adams

              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"?

              • 4. Re: Default Filter Selection
                Mark Holtz

                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...

                • 5. Re: Default Filter Selection
                  Cote Adams

                  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.

                  • 6. Re: Default Filter Selection
                    Mark Holtz

                    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.

                    • 7. Re: Default Filter Selection
                      Cote Adams

                      You're the man Mark. Thanks!