Return working days for each week of the year

[Ari_vzla]

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

 

[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 · Accepted solution]

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. 

[Ari_vzla]

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

 

[Ari_vzla]

Hi Fernando, 

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

[caiogil1]

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.

[caiogil1]

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.