topic Re: setting up a variable which will store only weekdays between 2 given dates in QlikView

setting up a variable which will store only weekdays between 2 given dates

sayadutt — Tue, 26 May 2015 16:45:21 GMT

Hi Team,

I have 2 date fields of the format m/d/yyyy.

Field1 = Planned Date

Field2 = Actual Date

Now I am setting up an variable to store the difference in these 2 dates:

Date#([Actual Date]) - Date#([Planned Date])

This expression works perfectly fine and returns the value in number of days.

Now I need this variable to calculate the difference of 2 dates based on weekdays only (ignoring Saturdays & Sundays)

So, if my Planned Date is 5/22/2015 and Actual Date is 5/26/2015, the variable should store 3 (Friday, Monday, Tuesday)

Please suggest.

Thanks

Re: setting up a variable which will store only weekdays between 2 given dates

swuehl — Tue, 26 May 2015 16:49:25 GMT

Look into Networkdays(StartDate, EndDate) function.

Re: setting up a variable which will store only weekdays between 2 given dates

sayadutt — Tue, 26 May 2015 18:33:58 GMT

Thanks Swuehl.

Is there a way where I can exclude US Holidays as well while calculating the date difference?

My dates ranges over the years, so cant hard code any particular date from a year.

Thanks

Re: setting up a variable which will store only weekdays between 2 given dates

rwunderlich — Tue, 26 May 2015 19:06:07 GMT

Look at the doc for NetWorkDays.

-Rob

Re: setting up a variable which will store only weekdays between 2 given dates

sayadutt — Tue, 26 May 2015 19:19:14 GMT

Hi Rob,

where can I find this doc?

Thanks

Re: setting up a variable which will store only weekdays between 2 given dates

swuehl — Tue, 26 May 2015 19:25:28 GMT

QV doesn't know any country specific or global holidays.

So you need a table of the relevant holidays. Then load the table into QV, concatenate the values to a string and assign to a variable and use the variable as third argument to Networkdays() function.

how to take holidays into account to calculate remaining workdays

Re: setting up a variable which will store only weekdays between 2 given dates

rwunderlich — Tue, 26 May 2015 19:25:30 GMT

Under "Help" in the QV menu.

-Rob