6 Replies Latest reply on Apr 13, 2018 12:46 PM by Stephen Groff

    Set START DATE, Not a Range in CUSTOM SQL

    Stephen Groff

      I am new to the SQL language, and I'm trying to figure out the best way to sort my data by a Start Date (on or after 10/01/2015)

       

      I've read several articles about setting parameters and such, but they all have to do with Date Ranges... I just need to tell my Data to populate these rows only if the EVENT_TS is on or after 10/01/2015

       

      EVENT_TS is my date.

       

      I've set a "Start Date" parameter, but I'm not sure how to add it in.

       

      If I am forced to use a date range, then how to I determine 'Today' as apart of the range?

       

      My query works, but I just need the date filter input.  Any help would be appreciated.

        • 1. Re: Set START DATE, Not a Range in CUSTOM SQL
          Joe Oppelt

          Create this calc:

           

          [EVENT_TS] >= [Start Date Parameter]

           

          Result is a Boolean.

           

          Put that on the filter shelf and select for TRUE.

           

          your sheet will only have dates >= the start parameter.

          • 2. Re: Set START DATE, Not a Range in CUSTOM SQL
            Stephen Groff

            So what you're telling me is that I cannot create this filter within the Custom SQL, but I'll have to do it outside of the Custom SQL query?

            • 3. Re: Set START DATE, Not a Range in CUSTOM SQL
              Joe Oppelt

              Ah.  I missed that detail...

               

              You can pull in a parameter in your custom SQL.  Do "Edit Data Source".  Go to the "Edit Custom SQL" editor.  At the bottom is a button to pull in your parameter.  So in my custom SQL here I have a hard-coded value (circled in blue) but I could take that out and insert the parameter there.

               

              1 of 1 people found this helpful
              • 4. Re: Set START DATE, Not a Range in CUSTOM SQL
                Stephen Groff

                I apologize for my ignorance...

                 

                So what I need to do is add this at the tail end of my statement:

                 

                AND EVENT_TS >= <parameters.start date>

                 

                ?

                 

                If that's the case, I better start trying to figure out how to correct my parameter.. the results won't populate:

                 

                Create parameter:

                Data Type: Date & Time

                Current Value: 1

                Display Format: Automatic

                Allowable Values: Range

                Set from Field: EVENT_TS

                MIN: ? (input my needed start date?)

                MAX: ? (let it auto-populate the max result?)

                Step Size: ? (no idea)

                 

                Thanks Joe.  Almost there!

                • 5. Re: Set START DATE, Not a Range in CUSTOM SQL
                  Joe Oppelt

                  Stephen Groff wrote:

                   

                  I apologize for my ignorance...

                   

                  So what I need to do is add this at the tail end of my statement:

                   

                  AND EVENT_TS >= <parameters.start date>

                   

                   

                   

                   

                  If you select it from the list that the pull-down button gives you, that's the format Tableau uses to shove it in there.  You could probably just type it in too.

                   

                  Stephen Groff wrote:

                   

                   

                   

                  If that's the case, I better start trying to figure out how to correct my parameter.. the results won't populate:

                   

                  Create parameter:

                  Data Type: Date & Time

                  Current Value: 1

                  Display Format: Automatic

                  Allowable Values: Range

                  Set from Field: EVENT_TS

                  MIN: ? (input my needed start date?)

                  MAX: ? (let it auto-populate the max result?)

                  Step Size: ? (no idea)

                   

                  Thanks Joe. Almost there!

                  I would just use "DATE" for the data type.


                  And what's with that current value of 1.  Date 1 I'm not sure what Tableau is doing with that.

                   

                  You can leave MIN and MAX blank.  (That comes into play if you are using the parameter on a dashboard and the user is going to select a date.  You don't want them picking a start date in the year 2100. 

                   

                  Leave step size blank too.  That comes into play if you are using a slider format for the user to select a date.  Sliders can be dragged, or the user can click the arrow at the end of each slider.  If the click the arrow, then the slider moves in that direction by the step size.

                   

                  "Set from field".  That's if you want to populate the list for the user.  Then the parameter4 becomes like a pull-down with all the valid values in a list.  If you populate from EVENT_TS, you will get all the unique dates in your database that are found in EVENT_TS among all the rows.

                  1 of 1 people found this helpful
                  • 6. Re: Set START DATE, Not a Range in CUSTOM SQL
                    Stephen Groff

                    I was wrong in my last when I said "Current Value" is 1.... The current value was actually the current date.

                     

                    When I changed the "Current Value" to 10/1/2015 and left everything that way it was... the following worked flawlessly.

                     

                    EVENT_TS >= <Parameters.Start.Date>

                     

                    Done and Done!  Thank you so much Joe!