topic Re: Working days Monday to Saturday in QlikView

Working days Monday to Saturday

Tue, 08 Oct 2013 08:13:22 GMT

Hi guys,

I am trying to work out the total number of working days in each month. For my application apart from Sunday everyday is a working day.

For example

Month Day Working Day No

Jan 1 Mon 1

Jan 2 Tue 2

Jan 3 Wed 3

Jan 4 Thu 4

Jan 5 Fri 5

Jan 6 Sat 6

Jan 7 Sun -

Jan 8 Mon 7

and so on for each month.

I have tried using networkdays but it only works with Monday to Friday and doesn't really give me what I need.

I am no QlikView expert, so I would appreciate some assistance.

Can someone help me out?

Thanks

Re: Working days Monday to Saturday

CELAMBARASAN — Tue, 08 Oct 2013 08:26:25 GMT

Create a work day flag like

if(Day<>'Sun', 1) AS WorkDayFlag

To calculate work day number in script

Left Join(TableName)

Load

DateField, //Assuming that you have date field in the table

if(Previous(Month) = Month, Peek('WorkDayNo') + 1, 1) AS WorkDayNo

Resident TableName

Where WorkDayFlag=1;

Re: Working days Monday to Saturday

Tue, 08 Oct 2013 08:40:40 GMT

Please refer the following application

Re: Working days Monday to Saturday

Tue, 08 Oct 2013 09:40:07 GMT

Thanks for your reply.

Load

DateField, //Assuming that you have date field in the table

if(Previous(Month) = Month, Peek('WorkDayNo') + 1, 1) AS WorkDayNo

Resident TableName

Where WorkDayFlag=1;

What does the ‘WorkDayNo’ in the Peek function relate to?

I tried different date values there, but it didn’t work as expected.

Secondly, I want to return the number of business days in each month and not the entire time period in the application.

So if January has 27 business days, Feb1 should be 1 and Feb 2 should be 2 and so on.

Thank you

Re: Working days Monday to Saturday

MK_QSL — Sat, 16 Nov 2013 20:02:16 GMT

Hi, Check my post on this...

http://community.qlik.com/docs/DOC-5314

Re: Working days Monday to Saturday

Sun, 17 Nov 2013 06:29:12 GMT

Hi ,

First use this Set statement in your Script

Set DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Then please try the code as below

=Round((MonthEnd (Today()) - MonthStart(Today()) ) ) -

(Floor((MonthEnd (Today()) - MonthStart(Today()) )/7 ) +

if( num( WeekDay(MonthStart(Today())))+1<= Match('Sun', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) and

Match('Sun', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) <=num( WeekDay(MonthEnd(Today())))+1 ,1,0))

Here i used current month i.e today() , you can replace by the date field for every month.

Please let me know if there is anything

Regards

Yusuf

Re: Working days Monday to Saturday

Mon, 18 Nov 2013 09:16:26 GMT

Thanks