Calculate the holidays in a given year

[datanibbler]

Hi guys,

I know it is possible to calculate the holidays and more than one guy have done it in MS_Excel - they can all somehow be calculated on the basis of Easter and Christmas. The thing is, basically every part of Germany has a different list of holidays (that are actually work-free days).

=> The question is, has anyone done this for QlikView?

Thanks a lot!

Best regards,

DataNibbler

[ashfaq_haseeb]

Hi,

You can define list of holidays in a variable.

Check if the attached file help you.

Have a look at this post too

Calculating Holidays in script

Regards

ASHFAQ

[datanibbler]

Hi Ashfaq,

ok, I can of course define a list of holidays in a variable.

The task is now to get a list of holidays - for Bavaria in this instance - from some calendar online or from Excel, whatever - every Jan1 or Jan2, without too much ado.

[ashfaq_haseeb · Accepted solution]

Hi,

May be like this.

Schedule this task yearly.

Regards

ASHFAQ

[marcus_sommer]

Hi DataNibbler,

I think you will need a table like this to match the date and the part from germany whereat the state might not excactly enough then some days will be different on postcode.

SpecialDaysComplete: // Feitags-Tabelle

Load * Inline [

DaySearch, DayResult, DayCluster, DayValue, Area

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++ feste einheitliche Feiertage

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

'01.01', Neujahr, feste einheitliche Feiertage, 0, "Alle"

'01.05', Maifeiertag, feste einheitliche Feiertage, 0, "Alle"

03.10, Nationalfeiertag, feste einheitliche Feiertage, 0, "Alle"

25.12, 1. Weihnachtsfeiertag, feste einheitliche Feiertage, 0, "Alle"

26.12, 2. Weihnachtsfeiertag, feste einheitliche Feiertage, 0, "Alle"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++ feste Sondertage

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

06.01, Heilige 3 Könige, feste Sondertage, "0,5", "BW,BY,ST"

31.10, Reformationstag, feste Sondertage, "0,5", "BB,MV,SN,ST,TH"

01.11, Allerheiligen, feste Sondertage, "0,5", "BW,BY,SL,NW,RP"

24.12, Heiligabend, feste Sondertage, "0,5", "Alle"

31.12, Sylvester, feste Sondertage, "0,5", "Alle"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++ variable einheitliche Feiertage

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-2, Karfreitag, variable einheitliche Feiertage, 0, "Alle"

-1, Ostersonnabend, variable einheitliche Feiertage, 1, "Alle"

0, Ostersonntag, variable einheitliche Feiertage, 0, "Alle"

1, Ostermontag, variable einheitliche Feiertage, 0, "Alle"

39, Christie Himmelfahrt, variable einheitliche Feiertage, 0, "Alle"

48, Pfingstsonnabend, variable einheitliche Feiertage, 1, "Alle"

49, Pfingstsonntag, variable einheitliche Feiertage, 0, "Alle"

50, Pfingsmontag, variable einheitliche Feiertage, 0, "Alle"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

+++ variable Sondertage

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-48, Rosenmontag, variable Sondertage, "0,5", "NW"

60, Fronleichnam, variable Sondertage, "0,5", "BW,BY,NW,SL,RP,HE"

] where left(DaySearch, 3) <> '+++';

- Marcus

[datanibbler]

That is extremely cool! Thank you!

I did know it was possible with QlikView to access web_contents, but I didn't know how and I never tried to up to now.

Given this website will be online for a while, that should work fine.

Thanks a lot!

Best regards,

DataNibbler

[ashfaq_haseeb]

Glad that it servers you

Regards

ASHFAQ

[datanibbler]

Hi Marcus,

that also looks good. Might be a solution once we actually get down to rolling out QlikView beyond this branch - then it will potentially be used outside Bavaria (well, in fact, every plant but this is outside Bavaria) and I am not sure whether every state has a site like this, so then I will need a different code.

For now, however, the variant with the website seems the easiest one. Let's see ...

Thanks a lot for the suggestion anyway - I'll copy it, in case the online one doesn't work ...

Best regards,

DataNibbler

[datanibbler]

Hi,

I jubilated too early, as it sometimes happens to  be - obviously, some setting of our firewall keeps the QlikView_server from accessing the internet. I get an error_message "cannot access HTML file", even when I try to run the original code.

So I'll have to build an inline_table like Marcus proposed.

@ marcus

I guess, from looking at it logically, that your > variable_holidays < are calculated based on Easter? So how do I get Easter to start with?

[marcus_sommer]

Hi DataNibbler,

this is my easter-calculation in a loop from vStart to vEnd:

let vOstern = round(num(date(makedate(year($(vStart) + $(i)), 4, day(minute(year($(vStart) + $(i)) / 38) / 2 + 55)), 'DD.MM.YYYY')) / 7) * 7 - 6;

- Marcus