Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Calculate the holidays in a given year


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

1 Solution

Accepted Solutions
ashfaq_haseeb
Champion III
Champion III

Hi,

May be like this.

Schedule this task yearly.

Regards

ASHFAQ

View solution in original post

14 Replies
ashfaq_haseeb
Champion III
Champion III

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
Champion
Champion
Author

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
Champion III
Champion III

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
Champion
Champion
Author


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
Champion III
Champion III

Glad that it servers you

Regards

ASHFAQ

datanibbler
Champion
Champion
Author

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
Champion
Champion
Author

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