
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use only workday dates in variable
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Take a look on: lastworkdate - script and chart function | Qlik Cloud Help

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For example something in this way:
let var = date(lastworkdate(today(), 1), 'YourSQLFormat');
and then:
sql select * from Source where DateField = '$(var)';

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
