topic Re: Networkdays function to change non working days from saturday-sunday to thursday-friday in QlikView

Networkdays function to change non working days from saturday-sunday to thursday-friday

Mon, 17 Sep 2012 20:56:07 GMT

Hi Folks,

I have a requirement where we need to calculate the no of days between start date and end data.

We can use the

networkdays( start:date, end_date {, holiday} )

but this function automatically excludes saturday and sunday.

What i need is to exclude Thurday and Friday as non working days and include the saturday and sunday as working day while doing the calculation.

Our client working days are Sat-Wed and Thu-Fri as weekOff.

So how can i use an logic to calculate the exact no of days.

Please advise how to achieve it.

Your suggestion is much appreciate.

Regards

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

MayilVahanan — Mon, 17 Sep 2012 21:10:04 GMT

Try like this,

Load *, if( WeekDay(DateField) = 'Thu' or WeekDay(DateField) = 'Fri', 0, 1) as Flag from tabkename;

Then Use count(Flag) as expression. You can achieve your requirement..

Hope it helps

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

swuehl — Mon, 17 Sep 2012 21:44:48 GMT

If you only have dates for start and end date, you will first need to somehow create a date record for each day in your period. If you could calculate the networkdays in the script, you can do it like this:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41160,41162';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

9/1/12 11:08 AM,9/8/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM, 7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM

];

// Create a record per day in your periods

TMP:

LOAD ID,

Date(daystart(DT1)+iterno()-1) as Date

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

// calculate the sum of days in your periods, that are not a Thu or Fri, or part of your Holidays (set variable vHol)

left join (INPUT) LOAD

ID,

count(Date) as Networkdays

Resident TMP where (WeekDay(Date)<3 or WeekDay(Date)>4) and not match(Date,$(vHol)) group by ID;

drop table TMP;

The Weekday() function returns 3 for Thursday and 4 for a Friday.

Hope this helps,

Stefan

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

Tue, 18 Sep 2012 09:10:15 GMT

Hi Stefan,

Thanks for the promp response to my query.

The solution provided by you was easy to understand and implement.

Regards,

Vasim

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

wizardo — Wed, 07 Jan 2015 08:36:56 GMT

Hi,

as i see it it works but it creates raws for each day in between the dates in each row of the source table.

if my source table is big and in avg i have 5-10 days diff in each row it means ill need to create a table 5-10 times larger.

this might be a problem

any other ways?

i need networkdays function to give sunday-thursday as working days (excluding friday and saterday)

Wizardo

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

marcus_sommer — Wed, 07 Jan 2015 14:35:58 GMT

Maybe you could use an approach like this:

count(distinct if(match(weekday(Date), 0,1,2,3,6), Date))

- Marcus

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

wizardo — Thu, 08 Jan 2015 12:23:05 GMT

yes ofcourse thats what i did

but that doesnt solve the problem of the large table case

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

marcus_sommer — Thu, 08 Jan 2015 12:29:34 GMT

It was meant as calculation within the gui. Further you didn't need create flags or calculations over the fact-table - better is to use a master-calendar.

- Marcus

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

wizardo — Thu, 08 Jan 2015 16:05:21 GMT

what will the master-calendar help here?

i have a fact with 2 date in each row and i need to calculate the working days between these dates.

i see no way to use the master-calendar in this scenario

normally id use the function NETWORKDAYS() but this functions assumes week where Monday is first day of the week and something that can work with weeks where Sunday is first day and Friday and Saturday are non working days.

and

swuehl

solution was the best i have found so far

(except the issue with large fact table about which i asked)

Wizardo

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

marcus_sommer — Thu, 08 Jan 2015 16:40:35 GMT

I don't know what do you want to do with these networkdays-values. If you don't calculate these values further within the script it could be better to perform such calculation on a small amount of data (restricted by selections) within the gui.

Nevertheless you could create an expression with a main-logic like this:

(enddate - startdate) - floor((enddate - startdate) / 7)

with an additional considering from the weekday from the startdate and enddate and a little bit fine-tuning.

I have built such expression some time ago but actually couldn't find it. Good luck.

- Marcus

Re: Networkdays function to change non working days from saturday-sunday to thursday-friday

Tue, 05 Apr 2016 09:28:24 GMT

Best way is take a primary key field from your data model tables..

And write the expression like

if(count(primarykey)>0 ,count(distinct date))

then it will display the working days of your customer data regardless sun,wed...etc