5 Replies Latest reply on Jul 8, 2016 1:39 AM by Dmitry Chirkov

    Issue with Parameter in Custom SQL

    Bobo Wieland

      I found an issue not emediatly visible too me that caused me some trouble with lower than expected SUM of values.

       

      Found out that this has to do with how Tableau uses Parameters in Custom SQL. I use Prameters to set a date interval in my sql query. Problem is that I store data with timestamps with microseconds and I format the endDate to disply it's value as "YYYY-MM-DD 23:59:59.999999". But for some reason Tableau fin it necessary to add the DATE()-sql function to my parameter causing the last day in my date range to allways be missing.

       

      I would say that this is a bug no matter the intentions behind this behaviour.

        • 1. Re: Issue with Parameter in Custom SQL
          Dmitry Chirkov

          What is the data type of your parameter and how exactly do you use in Custom SQL?

          Just direct comparison like WHERE MyDate < <Parameter.MyParam>?

          • 2. Re: Issue with Parameter in Custom SQL
            Bobo Wieland

            It's a timestamp column with microseconds so the format is as described above: 'YYYY-MM-DD H:i:s.xxxxxx'

             

            I use it with a BETWEEN statement in the query:
            WHERE created BETWEEN <Parameter.Start> AND <Parameter.End>

             

            Tableau then transaltes this to:

            WHERE created BETWEEN DATE('2016-06-01 00:00:00.000000') AND DATE('2016-06-30 23:59:59.999999')

             

            What I think is going on is Tableau trying to be smart, since I group the data: GROUP BY DATE(created) as well as selecting DATE(created) AS `date` and having the final datatype of the field I use in Tableau defined as a date column. But it is just wrong of Tableau to do it this way. Especially when it happends behind the scenes. There is no indication in the Custom SQL query window that any additional treatment of the data will take place.

            • 3. Re: Issue with Parameter in Custom SQL
              Dmitry Chirkov

              Tableau does not draw any parallels between usage of parameter in Custom SQL and any other fields.

               

              Need to ask agin - what's the data type of the parameters? Is it "Date" or "Date & Time"?

               

              Also, what are you connecting to?

               

              For MSSQL I see this:

                   WHERE [DateTime] BETWEEN {ts '1960-01-01 00:18:35.514'} AND {d '2016-07-13'}

              Start is "Date & Time" type and "End" is "Data" type so I get {ts ...} and {d ...} casts.

              1 of 1 people found this helpful
              • 4. Re: Issue with Parameter in Custom SQL
                Bobo Wieland

                You're absolutely right. I had the parameters set up as Dates.

                 

                I assumed too much about what was going on

                 

                I wanted a parameter that you would pick like a date (so no time involved). But then I assumed you could use the display format when using it.

                 

                I can use it as I want by having two Date Parameters and then use WHERE created BETWEEN <Parameter.Start> AND CONCAT(<Parameter.End>, ' 23:59:59.999999')

                 

                Thanks

                1 of 1 people found this helpful
                • 5. Re: Issue with Parameter in Custom SQL
                  Dmitry Chirkov

                  If you don't really need the time part then I suggest converting both parameters to Date and then use WHERE created BETWEEN <Parameter.Start> AND DATEADD(day, 1, <Parameter.End>)

                   

                  BETWEEN is an inclusive comparison but I wouldn't trust the precision you are getting from all these conversions...