Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
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?

Thank you so much

Arieli V

Labels (4)

• ### weekstart

1 Solution

Accepted Solutions
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:

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
6 Replies
Creator II

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

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:

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
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))

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?

Thank you so much

Arieli V

Contributor III
Author

Hi Fernando,

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)

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
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
Tags
Community Browser