How to exclude Saturday, Sunday & Public holiday?

[#1]

Hi Guys,
I'm new in qlikview.
We has a daily routine using feedback service report (SR).
SR is a form that will given to customer to fill in.
Expecting SR will return back to us within 3 days and only valid by counting base on working days (Mon-Fri).

We using Navision MySQL by pulling data from MSSQL

When report has generated from our Navision

LOAD
 "Printed SR No_",
 "Date SR Handover to Customer"
 "Date SR Received from Customer";

SQL SELECT *
FROM Database$Printing Tracking"

From above data in expression I just do:
"Date SR Received from Customer"- "Date SR Handover to Customer" then I will get total days.

But how do I exclude Saturday, Sunday and public holiday?

[prieper · Accepted solution]

Hi,

have a look into the NetWorkDays-function:

<h4>networkdays ( start:date, end_date {, holiday} )</h4>
Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps. <h2>Examples:</h2>
networkdays ('2007-02-19', '2007-03-01') returns 9
networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

HTH
Peter

[jonathandienst]

Frank

I have written a short series on the QV working date functions in my blog at http://yahqblog.blogspot.com/. They might help you in working this out (including how to take public holidays into account).

Jonathan

[nathanfurby]

Take a look at John Witherspoon's example in the following thread:

http://community.qlik.com/forums/p/27558/105413.aspx#105413

Works really well for me when working with holidays.

[#5]

DEAR ALL,
Thank you for your contributing answer to my question.
I need fix to exclude Saturday, Sunday and Public Holiday.

It seem Jonathan Dienst's answered more close to my question, because public holiday will get data from plain excel.

However, I have question for you Jonathan,

Base on your post blog at http://yahqblog.blogspot.com/2010/09/qlikview-working-day-functions.html

Is a great post! I still not sure how to put in into my qlikview.

On my qlikview script, I made two TAB

TAB1:
LOAD
 "Printed SR No_",
 "Date SR Handover to Customer"
 "Date SR Received from Customer";

SQL SELECT *
FROM Database$Printing Tracking"

TAB2:
tmpHoliday:
LOAD Date([DATE], 'dd-MM-yyyy') as Date
FROM [..\QVDATA\Public Holidays.xlsx]
(ooxml, embedded labels, table is Sheet1);

tmpConcat:
LOAD concat(chr(39) & Date & chr(39),',') AS HolidayDates
RESIDENT tmpHoliday;

Let vPublicHolidays = fieldvalue('HolidayDates',1);

DROP TABLE tmpHoliday;
DROP TABLE tmpConcat;

On my expression

From above data I create a chart table with dimension using "Printed SR No_",
And under expression I do:
"Date SR Received from Customer"- "Date SR Handover to Customer" then I will get total days.

To exclude Saturday, Sunday and public holiday where do I put this condition:
=NetWorkDays(MonthStart(Today()), Today(), $(vPublicHolidays)) ?

[#6]

Dear Jonathan,

When I reload the script it give me an error:

Error in expression:
CONCAT is not a valid function

What could be wrong? I'm using Qlikview version 7 at the moment.
I couldn't find CONCAT function on help either.

Frenky

I had change a bit the function on script as follow:

tmpHoliday:
LOAD Date([DATE], 'dd-MM-yyyy') as WorkingDate
FROM [%HOMEDRIVE%%HOMEPATH%\Excel\PublicHolidays.csv] (ansi, txt, delimiter is ',', embedded labels);

tmpConcat:
LOAD concat(chr(39) & WorkingDate & chr(39),',') AS HolidayDates
RESIDENT tmpHoliday;

Let vPublicHolidays = fieldvalue('HolidayDates',1);

DROP TABLE tmpHoliday;
DROP TABLE tmpConcat;

[jonathandienst]

Frank

Glad you found the blog post helpful.

I am afraid that the concat function is valid in V9, but apparently not in V7. I suggest that you consider upgrading to a more recent version.

Jonathan

[#8]

Is it, still possible to work with it version 7 instead?

I'm really dead end.

[jonathandienst]

Frank

The CONCAT function joins together all the field values using the second parameter as a delimiter. You could write your own I suppose, or construct it outside QV and read it from a text file.

What the function does is construct the list of quoted dates that will be considered non working days - in this format:

'2010/01/01', '2010/03/21', '2010/04/10', ..... '2010/12/25','2010/12/26'

But does QV7 support the NetWorkDays / FirstWorkDate / LastWorkDate functions?

Jonathan

[johnw]

Frank wrote:Is it, still possible to work with it version 7 instead?

The solution I posted in the thread mentioned earlier doesn't use concat(). It probably SHOULD, as that would be simpler and more efficient that the loop I used. But it doesn't, so perhaps it will work with version 7.