topic Re: NetworkDays in QlikView

NetworkDays

israrkhan — Tue, 17 Nov 2015 11:32:19 GMT

Hi Guys,

i have a requirement, where i have to show days between two dates.

i have data like this

No , SubmitDate , ResolvedDate, TotalDays?

for each number i have a submit and resolved date, i have to show the total days.

i am using NetWorkDays, but networkdays exclude Saturday AND sunday.

where i want to exclude only friday... saturday and sunday are the working days for us. friday is holiday

how to use networkdays in this case, or is there any other approach...?

i have rad this post on community..

but could not understand what it is doing..

NetWorkDays: Excluding Friday and Saturday between two dates

Many Thanks....

Re: NetworkDays

sunny_talwar — Tue, 17 Nov 2015 11:43:10 GMT

May be something like this if you are doing it in the front end:

=Count(If(WeekDay(Date(Today()-30+ValueLoop(0, 30, 1))) = 'Fri', Null(), 1))

Today() - 30 will be replaced by the start date and ValueLoop can be calculated based on End Date - Start Date.

Re: NetworkDays

jonathandienst — Tue, 17 Nov 2015 11:47:27 GMT

The Networkdays() function cannot be adjusted to your needs. What I would suggest is that you add a WorkingDay field to your master calendar, set to 1 for a working day and 0 for a non-working days. Then you can calculate the working days in a date range by summing the WorkingDay field.

Re: NetworkDays

israrkhan — Tue, 17 Nov 2015 11:48:02 GMT

Thank you Sunny

how to use in NetworkDays,

i am using like this,

only({<Type = {1}>} Networkdays(Submit_Date, Resolved_Date))

may i remove Networkdays? or do i use in networkdays..

Thanks

Re: NetworkDays

israrkhan — Tue, 17 Nov 2015 11:51:48 GMT

Dear Jonathan,

i have Master Calendar, and i have IsFriday Flag field,

for friday IsFriday = 1, and for all other days is 0...

i created the flag as if (WeekDay(Date) = 'Fri',1,0) as IsFriday

and i post a linked, how that works..? can you comment on that...?

Re: NetworkDays

jonathandienst — Tue, 17 Nov 2015 12:02:31 GMT

You need the inverse of that for the sum to work:

if (WeekDay(Date) = 'Fri',0,1) as WorkingDay,

Now sum WorkingDay

Re: NetworkDays

israrkhan — Tue, 17 Nov 2015 12:16:27 GMT

Thanks Jonathan,

i am showing this data in straight Table,

my Dimensions are Domain, Number , SubmitDate, Resolved_Date, Status

A, 001, 15-01, 2015 , 28 , 10, 2015 , Resolved

A , 002 , 10-10-2015 , 23-10-2015 , Met

i have this data structure , and my Is fiday flag is in MasterCalendar...

do you think it will work.. it will sum the days between two dats...

many thanks

Re: NetworkDays

sunny_talwar — Tue, 17 Nov 2015 12:58:15 GMT

Not the best of the solutions, and I am sure can be fine tuned further:

Total Days:

ResolvedDate-SubmitDate

ResolvedDate-SubmitDate+1

Fridays:

=SubStringCount(Concat(WeekDay(If(Date(SubmitDate + ValueLoop(0, 10000, 1)) <= ResolvedDate, Date(SubmitDate + ValueLoop(0, 10000, 1)))), ','), 'Fri')

Working Days:

=ResolvedDate - SubmitDate - SubStringCount(Concat(WeekDay(If(Date(SubmitDate + ValueLoop(0, 10000, 1)) <= ResolvedDate, Date(SubmitDate + ValueLoop(0, 10000, 1)))), ','), 'Fri')

=ResolvedDate - SubmitDate - SubStringCount(Concat(WeekDay(If(Date(SubmitDate + ValueLoop(0, 10000, 1)) <= ResolvedDate, Date(SubmitDate + ValueLoop(0, 10000, 1)))), ','), 'Fri')+1