Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
NetWorkDays
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.