Use only workday dates in variable

[ngoel]

Hello,

Is there a way we can only allow a variable to take weekday dates into it as a variable. What I am trying to do is basically below:

Assuming today is monday

Yesterday = Friday's date

Day before = Thursday's date

and then use these 2 variables, yesterday and day before into an SQL query in data load editor.

Appreciate the help!

Thanks,

Naman

[marcus_sommer]

Take a look on: lastworkdate - script and chart function | Qlik Cloud Help

[ngoel]

I don't understand how to use it from the help page. Is there an example where it is being passed as a variable in a query? The examples on the help page are creating there own table and then passing it as a formula in the table. I dont think I am able to translate that to my use case.

[marcus_sommer]

For example something in this way:

let var = date(lastworkdate(today(), 1), 'YourSQLFormat');

and then:

sql select * from Source where DateField = '$(var)';

[ngoel]

thank you so much!

Just once last question, and please pardon me if this is non sensible, I am very new to the tool. When you say 'YourSQLFormat' what do you mean here? Like the type of SQL tool I am using to get my data? 

Appreciate the help!

[marcus_sommer]

The data-type/format of the values within the data-base might be different to default-format from Qlik (defined with the interpretation-variables respectively without them are the OS region settings taken).

It depends on the data-base which syntax is accepted as valid - whereby you may also apply a converting on the sql-side, like:

et var = floor(lastworkdate(today(), 1);

and then:

sql select * from Source where cast(DateField AS int) = $(var);

The correct syntax depends on your data-base.

[ngoel]

I am probably doing something wrong, but even after using "yesterday = floor(lastworkdate(today(), 1);" my yesterday is still falling to a sunday whenever I run this on a monday, instead of falling to a friday

[marcus_sommer]

It's from your snippet not clear how the variable is created and then called. Beside this you could simply test the date-logic by applying the lastworkdate() within a text-box in the UI - if it worked like expected it could be transferred to the script and then it's just a matter of syntax.