how to find number of working days in each and every month?

[thabo2g5]

hi all

i'm doing  a project on availability so one of the requirement is to find number of days of working days and number of days of weekends in in a that particular month.

e.g  January 2017 have 22 working days , 4 saturdays and 5 sundays. i want to get this numbers using qlikview for each and every month.

thank you in advance

[swuehl · Accepted solution]

Create a master calendar, e.g. using something like this after your Facts table load:

MasterCalendar:

LOAD *,

      If(WeekDay <5, 1,0) as IsWorkingDay,

     If(WeekDay = 5, 1,0) as IsSaturday,

     If(WeekDay = 6,1,0) as IsSunday;

LOAD

  TempDate AS Date,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Weekday(TempDate) AS WeekDay,

  inyeartodate(TempDate, maxdate, 0) * -1 AS CurYTDFlag,

  inyeartodate(TempDate, maxdate, -1) * -1 AS LastYTDFlag

;

//=== Generate a temp table of dates ===

LOAD

  date(mindate + IterNo()) AS TempDate

  ,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate

;

//=== Get min/max dates from fact table ===/

LOAD

  min(Date)-1 as mindate,

  max(Date) as maxdate

RESIDENT Facts

;

Now you can easily e.g. create a table with Year / Month dimensions and count the respective WeekDay:

=Sum(IsWorkingDay)

=Sum(IsSaturday)

=Sum(IsSunday)

[vadim_grab]

NetWorkDays

[Peter_Cammaert]

Indeed.

If you don't need the flexibility of Stefan's solution (for example, in the - unlikely - situation that counts shouldn't change with selections in your calendar), you can do this in another way:

LET vStartDate = num(MakeDate(2015, 1, 1));

LET vEndDate  = num(MakeDate(2017, 12, 31));

WorkingDays:

LOAD *,

     DaysInMonth - WorkingDaysInMonth AS WeekendDaysInMonth;

LOAD MonthStart,

     Day(MonthStart) AS Day,

     Month(MonthStart) AS Month,

     Year(MonthStart) AS Year,

     num(Day(MonthEnd(MonthStart))) AS DaysInMonth,

     NetworkDays(MonthStart, MonthEnd(MonthStart)) AS WorkingDaysInMonth;

LOAD MonthStart(AddMonths($(vStartDate), IterNo()-1)) AS MonthStart

AUTOGENERATE 1

WHILE MonthStart(AddMonths($(vStartDate), IterNo()-1)) <= $(vEndDate);

NetworkDays() is a nice function, if only for allowing you to exclude holidays or any dates you consider as non-working. Of course, you could add that same feature to Stefan's MasterCalendar solution using a mapping table.