Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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.
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.
It's exactly what I need. Thanks a lot for your help!
best regards and have a nice day!