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

    Count Public Holiday Dates

    Brendt Wooi

      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

          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

            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
              Deran Naidoo

              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
                  Brendt Wooi

                  Good day qlikdeez.

                  Thank you for your reponse.

                  Currently my public holidays are in a stand alone table.

                    • Re: Count Public Holiday Dates
                      Deran Naidoo

                      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
                          Brendt Wooi

                          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
                              Deran Naidoo

                              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
                                  Brendt Wooi

                                  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
                                      Deran Naidoo

                                      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
                            Brendt Wooi

                            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