Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Ari_vzla
Contributor III
Contributor III

Return working days for each week of the year

Hi community, 

  I have an issue trying to get the working days in a week for during several weeks, I am using the expresion below

NetWorkDays(WeekStart([Creation Date Picking.autoCalendar.Week]), weekend([Creation Date Picking.autoCalendar.Week]),$(Holidays))

Creation date Picking.autoCalendar.Week is the range of dates and I have set up some days as holidays.

I get as result below graph, but the result is wrong since I had assigned  holiday in week  42 (10/12/2020) and W45 (11/02/2020) and the results is always the same, 5 days, even W42 and W45 must to be 4 days.

Some expert that can give me a solution or let me know what the issue is?

Picking Time- Qlik Sense.png

 

Thank you so much 

Arieli V

 

 

 

1 Solution

Accepted Solutions
caiogil1
Contributor III
Contributor III

Hi,

Your logic seems correct but I used a different approach (using min/max instead of weekstart/weekend) to get the result like bellow:

caiogil1_0-1605043035534.png

 

It is important that your variable Holidays is something like in the bottom of the image (variable = '01/01/2020', '02/01/2020', ...), each date between simple quotes and separated by comma like @Fernando_Fabregas suggested. 

I hope to learn a lot here and help if I can

View solution in original post

6 Replies
Fernando_Fabregas

Hi ! Do you have explicit simple quotes around every holiday in your string $(Holidays)?

Otherwise will be replaced with a division between 3 numbers... ie: 10/12/2020 = 0,000412541 and it will not match with any week.

Regards, Fernando

 

caiogil1
Contributor III
Contributor III

Hi,

Your logic seems correct but I used a different approach (using min/max instead of weekstart/weekend) to get the result like bellow:

caiogil1_0-1605043035534.png

 

It is important that your variable Holidays is something like in the bottom of the image (variable = '01/01/2020', '02/01/2020', ...), each date between simple quotes and separated by comma like @Fernando_Fabregas suggested. 

I hope to learn a lot here and help if I can

View solution in original post

Ari_vzla
Contributor III
Contributor III
Author

Thank you so much, your  solution works better than mine. After a few hours,  I found the problem with my expresion, I had to created a table returning the value to understood what happened. I do not  know why,  but weekstart expresion return dates in 1900, same for weekend. I had to created a new field with weekstart and weekend to got the correct date in order to get the correct solution.

NetWorkDays(WeekStart([Creation Date Picking.autoCalendar.Week]), weekend([Creation Date Picking.autoCalendar.Week]), $(Holidays))

Hoja - Qlik Sense.png

 In conclusion, your solution is easier than mine. Thank you so much. 

Now, I need show only the last 4 weeks, I am using expresion below using the part that you proposed

Count({<[Creation Date Picking.autoCalendar.Date] = {">=$(=WeekStart((Today()-28)))<= $(=WeekEnd((Today()-7)))"}>}[Confirmation time Picking])/(NetWorkDays(Min([Creation Date Picking.autoCalendar.Date]), max([Creation Date Picking.autoCalendar.Date]), $(Holidays))), 

but  I got  below graphs, I need clean the last first weeks and leave only last 4 weeks, do you have and idea how can I do it?

Qlik Sense.png

 Thank you so much

Arieli V

 

Ari_vzla
Contributor III
Contributor III
Author

Hi Fernando, 

  Yes, I am added a script with info about holidays.

caiogil1
Contributor III
Contributor III

How about use your first idea expression's in set analysis? Consider the range of dates From/To like bellow:

From: weekstart(today(), -4)

To: weekend(today(), -1)

caiogil1_0-1605063074420.png

Final formula
NetWorkDays(
min({<DateField={">=$(=weekstart(today(), -4))<=$(=weekend(today(), -1))"}>}DateField)
max({<DateField={">=$(=weekstart(today(), -4))<=$(=weekend(today(), -1))"}>}DateField)
$(vFakeHolidays))

Remind weekstart() points to Sunday and weekend() consider Saturday, but you can change that using the third parameter if needed.

I hope to learn a lot here and help if I can
caiogil1
Contributor III
Contributor III

Just a tip... don't use today() in the visualization layer. I prefer to save the function results in a variable during the Load Script, because if you don't run the load script for some days the today() is "updating" but your data is not.

I hope to learn a lot here and help if I can