topic Re: Current work days in month in QlikView

Current work days in month

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

Trying to calculate the amount of work days in the current month.

I need to account for the weekends and holidays. I currently have a string variable with all of my selected holidays defined as 'vHolidays'.

Here is the expression I am trying to use to achieve said count. The current amount of days for this month would be 20, 4th of july.

=networkdays({$<Date={"$(=FirstWorkDate(Today()-1,1,$(vHolidays)))"}, Month = {"$(=Month(Today()))"}>} min(Date), max(Date))

Re: Current work days in month

sunny_talwar — Mon, 10 Jul 2017 18:48:25 GMT

I think it should be like this:

=NetWorkDays(MonthStart(Max(Date)), Max(Date), $(vHolidays))

Re: Current work days in month

joshrussin — Mon, 10 Jul 2017 19:02:36 GMT

Used for formula, result was 5.

Changed the first max to min and result was 27.

there are 31 days in this month, only need to count business days and account for holidays. result should be 20

Re: Current work days in month

sunny_talwar — Mon, 10 Jul 2017 19:04:27 GMT

You want it for the whole month... try this

=NetWorkDays(MonthStart(Max(Date)), MonthEnd(Max(Date)), $(vHolidays))

Re: Current work days in month

joshrussin — Mon, 10 Jul 2017 19:14:25 GMT

Thanks!

Re: Current work days in month

joshrussin — Mon, 31 Jul 2017 16:51:25 GMT

Hey Sunny,

Any idea as to why this would now return 23 days?

Same formula, just now returns 23 instead of 20.

There are 23 workdays next month. but not sure why it would return next months result

Re: Current work days in month

sunny_talwar — Mon, 31 Jul 2017 16:57:00 GMT

Check this guy

Max(Date)

Does this give you July or August?

Re: Current work days in month

joshrussin — Mon, 31 Jul 2017 17:03:05 GMT

It returns 8/1/2017

Re: Current work days in month

sunny_talwar — Mon, 31 Jul 2017 17:09:16 GMT

So, I guess that is your issue, how are you getting 8/1/2017 to be your max date? Do you not expect that?

Re: Current work days in month

joshrussin — Mon, 31 Jul 2017 17:15:24 GMT

I would expect the max date to be the latest date. Which is 7/31/2017. I do not know how it is getting 8/1/2017

Re: Current work days in month

sunny_talwar — Mon, 31 Jul 2017 17:21:05 GMT

I would not know anything about this because I don't have any knowledge about your data. If Max(Date) = 08/01/2017, you will have to check in the database as to why you are getting this value....