topic Re: Working Day Count in a year in QlikView

Working Day Count in a year

tamilarasu — Wed, 17 Dec 2014 06:03:10 GMT

I am creating a calendar and need your suggestions on how to do the below. I just want to create a total working day in a year with few criteria.

* First day of the year should be 1. If it is holiday (WO column indicated as 0 (Sat, sun and holiday)) then next day should also 1. Likewise the count should go till year end. But if a month end (31-01-2012) is holiday or sun day, then the next day should incremented automatically.

* Again Next year should start with 1.

Please see screenshot for better understanding. I need to do exactly like below mentioned as "Need to do in QV"

Thank you in advance.

Re: Working Day Count in a year

tamilarasu — Wed, 17 Dec 2014 07:44:22 GMT

Any idea?

Re: Working Day Count in a year

maleksafa — Wed, 17 Dec 2014 07:46:52 GMT

you can use the networkdays function that calculates the number of working days between two dates and it can take optionally the holiday dates, you need to use this function while building your calendar script and loop over the networkdays to build the calendar.

Re: Working Day Count in a year

Wed, 17 Dec 2014 08:04:28 GMT

Hi,

try using

NetWorkDays(year_start_date,Year_end_date) which returns all the working days excluding weekends

could you just be little more precise with what your requirement is , like is the above mentioned table format is what you want in QlikView.

let me know...

Re: Working Day Count in a year

saumyashah90 — Wed, 17 Dec 2014 08:07:06 GMT

=NetworkDays(Start_Date,End_Date)

this will count no of working days.excluding satudays and sundays.

2)This wont count public holidays or local holidays which you might have to add it externally into expression

Re: Working Day Count in a year

tamilarasu — Wed, 17 Dec 2014 08:34:06 GMT

I need to create a column exactly what I have shown in the picture (Column name working days). I want to know, if a day is holiday then the working day count should same as previous day. For example, working day count of the date 04-01-2014 is 5, if suppose 05-01-2014 is holiday then the count of the next day is also 5 and 06-01-2014 is 6 like wise. Same rule for Saturdays and Sundays.I have a 3 years data, so bit confused on how to do this.

Re: Working Day Count in a year

Wed, 17 Dec 2014 13:48:08 GMT

1. NetWorkDays(FKDAT,FKDAT) returns 0 if its a weekend and returns 1 if its a week day

2. loop this as

if(NetWorkDays(FKDAT,FKDAT) ,use rangesum(workingDays,peek('workingDays')) as WorkingDays function.

try doing this may b it should work

let me know