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.

[#2]

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?

[Anonymous]

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

[Anonymous · Accepted solution]

See attached.

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

Hope it helps!

[#5]

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?

[Anonymous]

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.

[#7]

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