topic networkday calendar in QlikView

networkday calendar

swegener — Thu, 17 Sep 2009 19:27:11 GMT

Hi all,

I need to establish a networkday-calendar which contains besides the actual day some information concerning the networkday.

Within my script I generate a calendar which contains the day, week, month, year and so on.

Additionally, I need a table which contains the following information:

Date	Flag if it is a networkday or not	Cumulated network days concerning the current year	Number of networkdays within this month	Number of networkdays within this year
01.01.09	0	0	23	262
02.01.09	1	1	23	262
03.01.09	1	2	23	262
			23	262
31.01.09	0	23	23	262

Anybody got an idea how this can be realized via script?

networkday calendar

Oleg_Troyansky — Thu, 17 Sep 2009 20:24:17 GMT

There are two approached to creating a Calendar:

1. If you calendar is fairly standard, you can simply generate it within QlikView (using function autogenerate()) and calculate all the flags. I suspect that Holidays might need special attention - a "standard" calendar won't pay attention to Holidays.

2. If your calendar is customized, or if you have to take into account Holidays, - you might already have a Calendar table in one of your "back-office" systems. In that case, you'd need to lead that table into QlikView.

If you are generating the Calendar, you might have to do it in 2 or 3 steps:

1. Generate the calendar with the "daily" information.

2. Aggregate daily information to the monthly level and left join "monthly" information back into the Calendar.

3. Aggregate daily information to the Annual level and load "annual" information back into the Calendar.

good luck!

networkday calendar

Thu, 17 Sep 2009 20:34:13 GMT

The problem with calculating workdays always seems to be the holidays.

You can use the weekday() function to determine the day of the week (0 - 4 for Mon - Thur). Then you would need to exclude those days that were also holidays. On a past project, I created a table that would store holidays. When determining if a day was a workday, I checked against this list to make sure it wasn't a holiday. You may be able to create some logic to automatically generate your holiday list, but there always seems to be a difference between holiday days off and actual holiday dates.

As for the last two columns, you would then loop through your data and sum the flag column by month and year respectively.

networkday calendar

Thu, 17 Sep 2009 20:38:11 GMT

I guess it is a standard calendar.

So if there is something like

year	month	date
2009	January	01.01.2009
2009	january	02.01.2009
2009	January	03.01.2009

it is easy to calculate a flag whether it is a networkday or not.

So the result would be

year	month	date	networkday
2009	January	01.01.2009	0
2009	january	02.01.2009	1
2009	January	03.01.2009	1

Now I just thought the same way you did, calculation or aggregation on monthly level.

But the result of the code (just an example, not complete!)

sum(networkday) as result
group by month

is not

month	Result
January	2

it is

month	Result
January	1

Have you got an example / script?

networkday calendar

swegener — Thu, 17 Sep 2009 20:41:07 GMT

Thx for your proposals. At the moment I create a standard calendar in script like the following:

Datumsfeld:
load
date($(varMinDatum)+ITERNO()-1) as Datumsfeld
autogenerate(1)
while $(varMinDatum) + ITERNO()-1 <= $(varMaxDatum);

Kalender:
load
Datumsfeld,
week(Datumsfeld) as Week,
month(Datumsfeld) as Month,
year(Datumsfeld) as Year,
day(Datumsfeld) as Day,
NETWORKDAYS(Datumsfeld, Datumsfeld) as Arbeitstag
resident Datumsfeld;

In the field 'Arbeitstag' I get the working days Monday to Friday. That´s o.k. Unfortunately the accumulation on the field 'Arbeitstag' does not function at all. Holidays are another problem, on which I will pay attention later on.

Greetings.

networkday calendar

karensmith — Tue, 22 Sep 2009 22:23:19 GMT

swegener,

Interesting....I currently have the same requirement. My requiement includes calculating workday and cumulated networkdays. Can I ask you question? How do you calculated previous years networking days.

Also, I'm a little confused on how you are using Datsumfeld in the network days calculation. Do you have a sample application of how this working for you?

networkday calendar

Wed, 28 Oct 2009 04:58:15 GMT

I am also working through a similar issue, one suggestion I can make is to add to the calendar LOAD something like:

if(Weekday([yourdatefield])<5, 1, 0) as IsWorkDay,

Then you can sum IsWorkDay and at least get an accurate calculation for Months, Quarters, etc. (of course it always comes up '5' if you select a Week). Multiply by 8 to get AvailableWorkHours.

Of course the holiday issue is not addressed, need to join to another table to correct for that.

networkday calendar

Fri, 30 Oct 2009 01:08:19 GMT

May I suggest a differant approach, (Work By exception) :

1.Load a table of Public Holidays (DateKey)

2. Add/Concatenate a table of NonWorking Days (Sat Sun etc)

3.Add/Concatenate A Table of Non Working Hours (If Required)

4. Add Any CustomHolidays ( we dont work the last friday in a month, or Employee Holiday Dates)

5 Fill in the Gaps with a If not Exists in Non Working Days Table

6 Aggreagate Any Month Rollups etc

This way your calendar can always be regenerated ie if you close on mondays

Doing it in a qv script is a slightly differant matter

ColinR

networkday calendar

Tue, 16 Feb 2010 18:25:33 GMT

If somebody needs a qvd file, containing the network days of germany for the years 2006 up to 2016 he can get it via qlikview support. I generated a qvw and a qvd file for this purpose.

Case no: 00018627

Unfortunately i can not upload any data here 😞