4 Replies Latest reply on Jan 13, 2013 8:10 PM by Joshua Milligan

    Load Parameter values using custom sql code

    Scott Cabral

      Hi,

       

      I have a tableau workbook that is currently using a data extract.  The data is a month-end snapshot of insurance policy information, so there will be 1 row for each policy at the end of each month.  For example, if i have 10,000 policies on 12-31-2012, there will be 10,000 rows with a RUN_DATE of 12-31-2012.  If I have 11,000 polices on 1-31-2013, there will be another 11,000 rows with a RUN_DATE of 1-31-2013. 

       

      I want to create a parameter that will allow the user to filter the viz by selecting the "AS AT" date.  So, for example, if they want to see data as at 12.31-2012, they can select that date in the dropdown of the parameter.

       

      Right now I am hardcoding the dates every month (basically adding a new value at the end of the month to the parameter list).  I would like to create some code, possibly custom sql that will only select the distinct dates that exist in the table.  So if there are 2 months of data (Dec 2012 and Jan 2013), then only show 12-31-2012 and 01-31-2013 in the dropdown, but have it load the parameter dynamically each month.  So at the end of Feb 2013, it would automatically show 2-28-2013 in the list after the data gets loaded in the table.

       

      Is there a way to do this?

        • 1. Re: Load Parameter values using custom sql code
          Joshua Milligan

          Scott,

           

          The ability to have parameters that are dynamically updated is a highly requested feature, but is not currently available.

           

          In this case, however, why does a quick filter on the RUN_DATE not work?  I assume you would want everything up to and including a certain date.  That is possible on a date filter (use Range of Dates and then select Ending Date).  Maybe I'm missing something in your requirements that would prevent a quick filter from working.  If so, please let me know.

           

          Joshua

          • 2. Re: Load Parameter values using custom sql code
            Scott Cabral

            Thanks Joshua,

             

            So how would I add this quick filter to my dashboard as a drop down and updates all of the other workbooks that are part of my dashboard?  Also, when I right click on the Run_Date field and select 'Show Quick Filter', it automatically defaults to just the Year.  I need Month/Day/Year.

             

            thanks

            • 3. Re: Load Parameter values using custom sql code
              Scott Cabral

              Sorry,

               

              i also misunderstood my own requirements here.  The table has the last day of each month, however, i only need to show the date if it is a Quarter End Date (for example, Mar 31st, Jun 30th, Sep 30th, Dec 31st).

               

              i know that throws a little monkey wrench into this whole thing....

              • 4. Re: Load Parameter values using custom sql code
                Joshua Milligan

                Scott,

                 

                So, it sounds like the first step is to make sure you are filtered to only the end of the quarters.  That could probably be accomplished with a calculated field that would look something like:

                 

                      (Month([Date]) = 3 AND Day([Date]) = 31)

                OR (Month([Date]) = 6 AND Day([Date]) = 30)

                OR ...

                OR ...

                 

                If you create that calculation, add it to the filter shelf and set the filter condition to only give rows where it is true, you will only be working with end of quarter dates.

                 

                 

                As far as filtering a date, it's important to understand discrete vs. continuous dates (you might want to search and do some reading).  Tableau will default to a discrete date at the highest level of detail with multiple values (in your case, Year).  To override the default behavior, right click the field and change it to Continuous.  Then drag the field to the filter shelf and then it will prompt you as to how you want to filter it.  You can select a Range of Dates if you want to be able to specify a Start and/or End date.

                 

                If you want to attach a packaged workbook or even just some sample data, I'd be happy to take a look to see if there is anything else that might help!

                 

                Joshua