6 Replies Latest reply on Jun 24, 2011 2:28 PM by James Baker

    Date Functions and User Parameters

    Mark Chappell

      I'm trying to use a user parameter with the DATEADD/DATETRUNC functions and am getting tagged with a calculation error stating that the first first argument needs to be a string literal.

       

      I can use an integer parameter for the DATEADD interval, however.

       

      Can user parameters be utilized in the date (or other) functions within a calculation?

       

      Thanks,

      Mark Chappell

      eBI Systems, LLC

        • 1. Re: Date Functions and User Parameters
          Richard Leeke

          Yes, I tried to do that a couple of days ago - it would be handy if you could parameterise that, but it seems you can't.  I'm not quite sure why.

           

          I just did it the more long-winded way - I created myself a calculated field which truncates according to a parameter ([datepart]):

           

          CASE [datepart]

          WHEN 'year' THEN DATETRUNC('year', [Date])

          WHEN 'quarter' THEN DATETRUNC('quarter', [Date])

          WHEN 'month' THEN DATETRUNC('month', [Date])

          WHEN 'week' THEN DATETRUNC('week', [Date])

          WHEN 'day' THEN DATETRUNC('day', [Date])

          WHEN 'hour' THEN DATETRUNC('hour', [Date])

          WHEN 'minute' THEN DATETRUNC('minute', [Date])

          WHEN 'second' THEN DATETRUNC('second', [Date])

          ELSE [Date]

          END

          • 2. Re: Date Functions and User Parameters
            Richard Leeke

            But you can use a parameter for the increment in a DATEADD(), as Mark noted.

             

            I suspect it may be that the SQL expression which Tableau has to generate varies depending on the date part, and Tableau needs to know what SQL it's going to be generating before the parameter is set, whereas the increment is just a value which needs to be substituted immediately before execution.  Or something along those lines.

             

            Re-reading your comment, Joe, that may be exactly what you meant anyway.  ;-)

            • 3. Re: Date Functions and User Parameters
              Joe Mako

              While it is just my guess that this is what Tableau is doing in the background, yes, we are thinking along the same lines. :)

               

              It is only the  "date_part" argument that has the requirement of needing a statically set string and cannot use a parameter in its stead, while the "interval" argument can be a parameter. Different arguments have different restrictions.

              • 4. Re: Date Functions and User Parameters
                Joe Mako

                I believe the reason why you cannot have a parameter as the "date_part" arguments of the "DATE" functions is because the parameter is not evaluated before the SQL is generated, while the DATE functions are converted to the SQL for the data source.

                 

                I think it would be great if there was some logic built into Tableau that attempted to evaluate parameters and the formulas they are a part of before generating the SQL.

                • 5. Re: Date Functions and User Parameters
                  Mark Chappell

                  Thanks guys...I believe I'll add this item to the Tableau "Wouldn't it be nice..." list. In the meantime, I'll go with the "long-winded" solution...

                  • 6. Re: Date Functions and User Parameters
                    James Baker

                    I have logged this with engineering as a bug - my guess is that we can do better.  Thanks, Mark et. al.