4 Replies Latest reply: Aug 14, 2009 4:20 AM by Michael Solomovich RSS

    Workdays calculation in pivot table / grouped dimensions

    alexeyaushev

      Hello, friends!

      I'm tring to resolve following task:

      I have a grouped time dimension (e.g. day / week / month / year) and I have a measure, which depends on number of workdays in corresponding number of workdays in a choosen period.

      The idea was to calculate number of workdays via 'networkdays' function, passing it starting and ending days of the period. But how to define the period? What is now choosen in grouped dimension?

      May be it is too complex way, but I still did not invent how to make it in a script =)

      I would be very pleased to you for any input!

      Kind regards, Alexey

        • Workdays calculation in pivot table / grouped dimensions
          Michael Solomovich

          Alexey,
          I'm not sure what you mean here - "how to define the period". But I can tell how I counted the number of workdays before QlikTech created the networkdays() function.
          Take a look at my post on Wiki "how to create a calendar". For workdays, I used additional field
          if(weekday(DateId)<5,1,0) as Workday
          (Because Saturday is 5, and Sunday is 6.)
          Now, to count the number of workdays on the front end, use expression sum(Workday).
          Hope it helps, or at least gives some idea.

            • Workdays calculation in pivot table / grouped dimensions
              John Witherspoon

              Let me see if I understand what you want. Let's say we select March 12, 2009 through July 15, 2009. If we use "day" for the time dimension, we'd want the number of work days between March 12, 2009 and July 15, 2009. If we use "month" for the time dimension, we'd want the number of work days between March 1, 2009 and July 31, 2009. If we use "year" for the time dimension, we'd want the number of work days between January 1, 2009 and December 31, 2009. Is that what you want?

                • Workdays calculation in pivot table / grouped dimensions
                  alexeyaushev

                  To Michael Solomovich:

                  Thank you, Michael, for useful comment. I've read your wiki article and will use the calendar :)

                  To John Witherspoon:

                  May be even easier. My task could be solved if I had a measure [Number of workdays], correctly calculated for each time dimension in Calendar. Then in spite of measure choosen Sum([Number of workdays]) could give correct answer (as Michael said).

                  Next issue is, that if(weekday(DateId)<5,1,0) does not take into account any holidays. Is there variable, containing national holidays for diff. countries? Or we have to create a list and check it additionally?

                    • Workdays calculation in pivot table / grouped dimensions
                      Michael Solomovich

                      Well, the convinience of the networkdays function is that it allows to exclude holidays. I would probably do it as a list of variables, one for each holiday, some of them constants (e.g. New Year Day as January 1), others floating (e.g. Labor Day as first Monday of September). Holidays based on the Moon calendar, like Easter, are more tricky, but there are functions to help with it too. If a time period is multiple years, each variable should return comma-separated list of dates.
                      (Just thinking "loud"...)