How to assign @variables in tableau

I am trying to use the following in Tableau to declare date variables, which I later call them in the SQL code.

But I am unable to do the same in Tableau. Can you please help/guide me in the right direction to use these functions

Tableau Declare Error Message

-- Declare the parameter dates and get the start date and date for all queries.
Declare @ParamDate as date = '2018-12-31';
Declare @Trailing90Days as date = DATEADD(DAY,-90,GETDATE());
Declare @MonthStartDate as date = DATEADD(DAY, 1, EOMONTH(@ParamDate, -1));
Declare @MonthEndDate as date = EOMONTH(@ParamDate);
Declare @YearStartDate as date = DATEADD(yy, DATEDIFF(yy, 0, @ParamDate), 0);
Declare @YearEndDate as date = DATEADD (dd, -1, DATEADD(yy, DATEDIFF(yy, 0, @ParamDate) +1, 0));
Declare @YoYMonthStartDate as date = DATEADD(YEAR, -1, @MonthStartDate);
Declare @YoYMonthEndDate as date = DATEADD(YEAR, -1, @MonthEndDate);
Declare @MoMMonthStartDate as date = DATEADD(MONTH, -1, @MonthStartDate);
Declare @MoMMonthEndDate as date = DATEADD(MONTH, -1, @MonthEndDate);
Declare @QoQMonthStartDate as date = DATEADD(Quarter, -1, @MonthStartDate);
Declare @QoQMonthEndDate as date = DATEADD(Quarter, -1, @MonthEndDate);

select 
        @ParamDate as ParamDate, 
        @MonthStartDate as MonthStartDate, 
        @MonthEndDate as MonthEndDate,
        @YearStartDate as YearStartDate,
        @YearEndDate as YearEndDate,
        @YoYMonthStartDate as YoYMonthStartDate,
        @YoYMonthEndDate as YoYMonthEndDate,
        @MoMMonthStartDate as MoMMonthStartDate,
        @MoMMonthEndDate as MoMMonthEndDate,
        @QoQMonthStartDate as QoQMonthStartDate,
        @QoQMonthEndDate as QoQMonthEndDate,
        @Trailing90Days as Trailing90Days

​x
 
-- Declare the parameter dates and get the start date and date for all queries.Declare @ParamDate as date = '2018-12-31';Declare @Trailing90Days as date = DATEADD(DAY,-90,GETDATE());Declare @MonthStartDate as date = DATEADD(DAY, 1, EOMONTH(@ParamDate, -1));Declare @MonthEndDate as date = EOMONTH(@ParamDate);Declare @YearStartDate as date = DATEADD(yy, DATEDIFF(yy, 0, @ParamDate), 0);Declare @YearEndDate as date = DATEADD (dd, -1, DATEADD(yy, DATEDIFF(yy, 0, @ParamDate) +1, 0));Declare @YoYMonthStartDate as date = DATEADD(YEAR, -1, @MonthStartDate);Declare @YoYMonthEndDate as date = DATEADD(YEAR, -1, @MonthEndDate);Declare @MoMMonthStartDate as date = DATEADD(MONTH, -1, @MonthStartDate);Declare @MoMMonthEndDate as date = DATEADD(MONTH, -1, @MonthEndDate);Declare @QoQMonthStartDate as date = DATEADD(Quarter, -1, @MonthStartDate);Declare @QoQMonthEndDate as date = DATEADD(Quarter, -1, @MonthEndDate);​select         @ParamDate as ParamDate,         @MonthStartDate as MonthStartDate,         @MonthEndDate as MonthEndDate,        @YearStartDate as YearStartDate,        @YearEndDate as YearEndDate,        @YoYMonthStartDate as YoYMonthStartDate,        @YoYMonthEndDate as YoYMonthEndDate,        @MoMMonthStartDate as MoMMonthStartDate,        @MoMMonthEndDate as MoMMonthEndDate,        @QoQMonthStartDate as QoQMonthStartDate,        @QoQMonthEndDate as QoQMonthEndDate,        @Trailing90Days as Trailing90Days​

Answer

I’m assuming you’re trying to use Custom SQL – you don’t actually say it but the code and the error message suggests it.

Tableau “wraps” custom SQL in a select like this:

SELECT * FROM
( <custom sql goes here> ) a

 
SELECT * FROM( <custom sql goes here> ) a​

Which is why your code isn’t working as you think it should.

Also, your parameter is hard coded within the SQL itself. I’m guessing you’re going to replace this with a Tableau parameter in due course.

So the short answer is, you can’t use DECLARE like you intend. The long answer is that without declaring the variable in SQL you just have to refer to the parameter value all the time within your custom SQL.

Try NOT to use custom SQL though, it only makes Tableau slower than what it should be because you’re forcing it to run your code when it might know that there’s a faster option.

Advertisement

Answer