topic Count Public Holiday Dates in QlikView

Count Public Holiday Dates

Tue, 06 Dec 2016 07:17:38 GMT

Good day.

i am hoping that somebody could assist me.

i have a table with all public holiday dates for each year. i am needing to count how many dates there are in the selected year and month.

The reason for this is to be able to calculate how many trading days we have in a selected year or month by calculating the Networking and subtracting any public holidays.

i have tried the following expression which is giving me a problem.

the table contains only a list of dates.

=Count({<year(Date(Max(PublicHoliday))) = {'=$(FinYear)'}>} [PublicHoliday])

any help would be appreciated.

Re: Count Public Holiday Dates

marcus_sommer — Tue, 06 Dec 2016 07:52:39 GMT

I would use a different way by flagging the working days within the master-calendar with 1 - excluding Sa. and Su. per weekday() and further excluding holidays per applymap() - and then using something like: sum(WorkingDaysFlag) within your objects. An example how it could be done could you find here: Master Calendar with movable holidays.

- Marcus

Re: Count Public Holiday Dates

Gysbert_Wassenaar — Tue, 06 Dec 2016 07:54:03 GMT

year(Date(Max(PublicHoliday))) = {'=$(FinYear)'

Is FinYear a variable or a field? If it's a field then you've got things the wrong way around:

FinYear={"$(=Year(Max(PublicHoliday)))"}.

If FinYear is a variable then you need something like: PublicHoliday={"=Year(PublicHoliday)=$(FinYear)"}

Re: Count Public Holiday Dates

Anonymous — Tue, 06 Dec 2016 07:58:12 GMT

Is your public holidays field in the same table as your calendar or your fact table or is it a stand alone table with just public holidays? how is it linked to the rest of the model?

Re: Count Public Holiday Dates

Tue, 06 Dec 2016 08:02:01 GMT

Good day Marcus.

thank you for your response.

i will look into this, i am still very new to Qlikview so still learning best practices

Re: Count Public Holiday Dates

Tue, 06 Dec 2016 08:02:58 GMT

Good day gysbert.

Thank you for your response.

FinYear is a vairable.

Re: Count Public Holiday Dates

Tue, 06 Dec 2016 08:03:55 GMT

Good day qlikdeez.

Thank you for your reponse.

Currently my public holidays are in a stand alone table.

Re: Count Public Holiday Dates

Anonymous — Tue, 06 Dec 2016 08:17:29 GMT

I would say, left join it to the date field you want to calculate against if you dont have a calendar that is linked to the rest of the model. Make sure the date formats are the same for the joins

Instead of

Count({<year(Date(Max(PublicHoliday))) = {'=$(FinYear)'}>} [PublicHoliday])

count({<YourYearField = {'$(FinYear)'}, YourMonthField = {'$(vMonthVariable)'}>} Distinct [PublicHoliday])

> to get the number of public holidays within the year month selections.

count({<YourYearField = {'$(FinYear)'}, YourMonthField = {'$(vMonthVariable)'}>} Distinct [ActualDaysInMonthField])

count({<YourYearField = {'$(FinYear)'}, YourMonthField = {'$(vMonthVariable)'}>} Distinct [PublicHoliday])

to get the Tradingdays

Re: Count Public Holiday Dates

Tue, 06 Dec 2016 15:48:10 GMT

Good day qlikdeez.

Thank you for your reply. apologies for the late reply, i had to leave the office.

my FinYear variable is a variable that sets the latest Financial Year on Load, i do not have any variables for months, how do i count public holidays in the selected month and not from a variable?

Re: Count Public Holiday Dates

Anonymous — Wed, 07 Dec 2016 06:18:39 GMT

Hi,

leave out this part of the expression ",YourMonthField = {'$(vMonthVariable)'}"

If your month is selected then Set analysis will allow the expression to consider your selection on Month and Other fields when calculating except for the year which you are setting to FinYear

Hope that helps!

Re: Count Public Holiday Dates

Wed, 07 Dec 2016 06:31:17 GMT

Good day qlikdeez.

Thank your for your reply.

i did try that yesterday by leaving out the month, the expression was ok but the count remained 0.

mt variable finYear is just the year not a date, while the public holiday is a date field should that still work?

another thing our Financial years are setup one year ahead (eg. in 2016 our FinYear is 2017) i also tried to deduct 1 when trying to do the analysis but the count still remained 0

what am i doing wrong here?

Re: Count Public Holiday Dates

Anonymous — Wed, 07 Dec 2016 08:28:10 GMT

If you have correctly left joined the PublicHoliday field to the table that contains the Year you want to work with..

count({<YourYearField = {'$(FinYear)'>} Distinct [PublicHoliday])

in the above YourYearField should be in the same format of FinYear variable i.e YYYY

on the front end drop an inputbox to see if FinYear is populated

Also check YourYearField to see if the format matches FinYear

if not convert it i.e year(YourYearField)

PublicHoliday can be in a different format i.e DD/MM/YYYY but the count should work

Re: Count Public Holiday Dates

Anonymous — Wed, 07 Dec 2016 09:34:22 GMT

See attached.

This will give you an idea how to get it done...

Hope it helps!

Re: Count Public Holiday Dates

Wed, 07 Dec 2016 14:38:52 GMT

Good day Deran.

Thank you for your reply.

i have changed my public holiday table structure to include the year for each public holiday date.

when i try and use your above example i get error "Error in set modifier ad hoc element list: ',' or ')' expected'

this is what i am using.

=count({<PublicHolidayFinYear = {'$(FinYear)'>} Distinct [PublicHoliday])

i have brought 'PublicHolidayFinYear' field to match my FinYear variable.

what am i doing anything wrong?

Re: Count Public Holiday Dates

Anonymous — Thu, 08 Dec 2016 06:40:33 GMT

Hi,

Check the attachement posted previously to get an idea of how to link up and flag the public holidays.

Also check the expression setup.

In the example, I just used random dates to flag as public holiday.

Re: Count Public Holiday Dates

Thu, 08 Dec 2016 14:09:24 GMT

Good day.

Thank you to all who have helped me with this, i have managed to get the correct counts with a mixture of all suggestions. qlikdeez, thank you so much for your example app, it has really helped me.

you guys are awesome