Count Public Holiday Dates

[#1]

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.

[marcus_sommer]

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

[Gysbert_Wassenaar]

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)"}

[Anonymous]

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?

[#5]

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

[#6]

Good day gysbert.

Thank you for your response.

FinYear is a vairable.

[#7]

Good day qlikdeez.

Thank you for your reponse.

Currently my public holidays are in a stand alone table.

[Anonymous]

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

[#9]

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?

[Anonymous]

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!