15 Replies Latest reply: Dec 8, 2016 9:09 AM by Brendt Wooi

# Count Public Holiday Dates

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

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

Good day Marcus.

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

• ###### Re: Count Public Holiday Dates

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

Good day gysbert.

FinYear is a vairable.

• ###### Re: Count Public Holiday Dates

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

Good day qlikdeez.

Currently my public holidays are in a stand alone table.

• ###### Re: Count Public Holiday Dates

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

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])

• ###### Re: Count Public Holiday Dates

Good day qlikdeez.

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

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

Good day qlikdeez.

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

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

See attached.

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

Hope it helps!

• ###### Re: Count Public Holiday Dates

Good day Deran.

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

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

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