7 Replies Latest reply on Feb 5, 2015 7:34 AM by Mark Lake

    Set filter value based on a database query

    william raffaele

      Have a filter which needs to change every month when after the monthly job is run.  Would like this to happen automatically, based on a run date in the database.  Right now, we have to change the program each month to correspond to the latest run date. 

       

      Maybe a separate datasource/query just to get the max(run_date), then set calculated field, then set filter.

       

      1.  Is this the right approach?

       

      2.  Is there an example on the web?

       

      Thanks for your help.

        • 1. Re: Set filter value based on a database query
          Mark Lake

          William,

           

          You should be able to accomplish this with a little trickery!  You do not need to create a new dataset, you can have Tableau use your dataset twice, sort of like a self join in SQL terms.

           

          Assume:

          • Your Tableau data connection has a dataset called "MonthlyJob" and the date the job was run is a date column called "RunDate"
          • You have already built a Viz.  A good test would be a text table with RunDate dimension and Sum(Number of Records) Measure

           

          Steps to take

          • Click on Data / MonthlyJob / Duplicate
            • You will now see a new dataset called MonthlyJob (copy)
          • Create a new Calculated Field in the MonthlyJob dataset called "SelectMaxDate"
            • Formula to use is "attr([RunDate]) = total(max([MonthlyJob (copy)].[RunDate]))
            • This will be a boolean Measure field, with the "T | F" Icon
          • Drag and drop the SelectMaxDate field into Filters
          • A dialog box will pop up with the two possible choices - True and False
          • Check the True option and your Viz will adjust to filter out all but the latest RunDate values

           

          Notes:

          • You may need to adjust the "Compute Using" property, depending on your final viz characteristics, but I do not think it will matter
          • You should definitely validate that the duplicated data set did not result in a cartesean product.  In my test data I have multiple records per day and this method did not result in any duplicated data
          • Depending on the size of your data, I do not know if you will hit performance problems

           

          Hope this helps!

          • 2. Re: Set filter value based on a database query
            william raffaele

            Thanks for your help!

             

            As long as I have a 2nd dataset, might as well just get the max from a special query, instead of getting every row, then doing a max.  That will eliminate the extract overhead on the database.

             

            Moving thru the rest of your solution... 

            • 3. Re: Set filter value based on a database query
              william raffaele

              Question about your solution:

               

                     attr([RunDate]) = total(max([MonthlyJob (copy)].[RunDate]))


              Is this setting the calculated field?  Or is it returning a boolean based on whether the Run date is equal to the max run date?

              • 4. Re: Set filter value based on a database query
                Mark Lake

                It will return a boolean.

                 

                That being said, this solution would only work if you have the date on your vizualization (just realized that).  So, it will not work in all cases.

                 

                I think your best bet is to create a second data set that just has the max date in it and then in your data model, join the 2 tables which will filter the data for you.

                 

                Sorry for the partial solution there :/

                • 5. Re: Set filter value based on a database query
                  william raffaele

                  Think I got it.  You put me on the right track.

                   

                  Did it all in one datasource. 

                   

                  Added a custom query which pulls in the max date tied to some common field.  Join to main table based on common field.  Now max date (most recent run date is in every row returned)

                   

                  Added a calculated field, test date against  the max date.

                   

                  Remove the old filter/use calculated field as the filter

                  • 6. Re: Set filter value based on a database query
                    Mark Lake

                    Awesome!

                     

                    It is great if you can manipulate the input data like that before hitting Tableau.  Sometimes we get so caught up in making the tool do everything (especially in places where getting DB changes is like moving a mountain)

                    • 7. Re: Set filter value based on a database query
                      Mark Lake

                      OK, so, this was gnawing at me as "there has got to be a simpler way"...

                       

                      And there is!

                       

                      You can create a set that will do this, no need to manipulate your incoming dataset with the max date or anything like that.

                       

                      • Right Click on your date field
                      • Choose Create Set
                      • Give it an appropriate name
                      • Click on Top tab
                      • Click on "By Field" button
                      • Change Top 10 to Top 1
                      • Change Number of Records to your date field
                      • Change Sum to Maximum
                      • Click OK
                      • Drag your set onto the filter shelf and you are good to go