Make a date dynamic

[rm1]

Hello Everyone!

I'm trying to build a formula led by a dynamic data range. I need to calculate the working days of the current year and I use the following formula (I need to exclude the 2 dates in 3rd and 4th position because they are holiday days in my Country). 

NetWorkDays ('01/1/2020', '12/31/2020',  '01/01/2020',  '1/6/2020')

My problem is that I should be able to make the dates "up to date" for the next years. I need something like:

NetWorkDays ('01/1/"dynamic year"', '12/31/"dynamic year"',  '01/01/"dynamic year"',  '1/6/"dynamic year"') in order to have 2021 next year and so on.

Can anyone help me?

thanks in advance!

[stevedark]

Hi @rm1 

You can inject a variable into code using dollar expansion, this will work:

let vYear = 2021;

NetWorkDays ('01/1/$(vYear)', '12/31/$(vYear)',  '01/01/$(vYear)',  '1/6/$(vYear)')

I expect you will hit a problem though that different years have different public holidays?

At the end of this blog post I share an example where holiday days are loaded from a spreadsheet and then injected into a NetworkDays expression stored in a variable. This may help with what you are trying to achieve:

https://www.quickintelligence.co.uk/variables-parameters-load-script/

Good luck!

Steve

[rm1]

Hi @stevedark 

thanks for your hint. My problem is that I am not able to create variables or to operate on the script due to my Company's security restrictions. I can only work on Set Analysis Expressions.

Therefore I need to find a way to make the year dynamic using only Qlik date functions.

[stevedark · Accepted solution]

Hi @rm1 

If you can't use variables you are in a sticky position about how you go about changing the year. If you want to use the current year you can do this:

NetWorkDays ('01/1/$(=year(today()))', '12/31/$(=year(today()))',  '01/01/$(=year(today()))',  '1/6/$(=year(today()))')

The equals symbol allows you to execute code and inject it into an expression.

If you wanted to go for the latest year in the selection that would be:

NetWorkDays ('01/1/$(=max(Year))', '12/31/$(=max(Year))',  '01/01/$(=max(Year))',  '1/6/$(=max(Year))')

This is where Year is a field in your data model.

Hope that helps.

[rm1]

It's exactly what I need. Thanks a lot for your help!

best regards and have a nice day!