Accumulated Number of Working Days in a Month

[stanlyrj]

Hi guys,

I want to count the number of working days in a month in a format that given below. I need to calculate this in the script.

Month    Natural Day    Working Day    Total Working Days

jun              1                        1                             26

jun              2                        2                             26

jun              3                        3                             26

jun              4                        3                             26

jun              5                        4                             26

jun              6                        5                             26

jun              7                        6                             26

jun              8                        7                             26

jun              9                        8                             26

jun              10                      9                             26

jun              11                      9                             26

jul              12                       10                           26

Can anyone help me to find a solution for this problem?

Thank you

Stan

[Anil_Babu_Samineni]

Is this static or do we consider as Date filed

[stanlyrj]

Hi Anil,

Date is a field in here.

Please consider the below attached .qvs file as a reference for master calendar script.

Thank You

Stan

[stanlyrj · Accepted solution]

Hi Anil,

I have solved the issue by using the following script.

HolidayTmp:

  LOAD Date

  FROM

  [.\Import\Holidays.xlsx]

  (ooxml, embedded labels, table is Holidays);

  ConcatTmp:

  LOAD Concat (chr(39) & Date & chr(39),',') as HolidayDates

  Resident HolidayTmp;

  Let vPublicHolidays = FieldValue('HolidayDates',1);

  Let vCurMonth = Month(Today());

  Dates:

  LOAD date(FieldValue('$(DateField)', RecNo())) AS TempDates    

  AUTOGENERATE FieldValueCount('$(DateField)')

  ;

  Dates_Range:

  LOAD

  Max(TempDates) AS MaxDate,

  Min(TempDates) AS MinDate

  Resident Dates

  ;

  Drop table Dates;

  LET vDateMin = Num(Peek('MinDate', 0, 'Dates_Range'));

  LET vDateMax = Num(Peek('MaxDate', 0, 'Dates_Range'));

  DROP TABLE Dates_Range;

  TempCalendar: 

  LOAD

  $(vDateMin) + RowNo() - 1 AS DateNumber

  AUTOGENERATE 1 

  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax)

  ; 

  Calendar: 

  LOAD

  DateNumber AS $(DateField),

  Date(DateNumber) AS Date,

  Day(DateNumber) AS Day,

  Weekday(DateNumber) AS WeekDay, 

  Week(DateNumber) AS Week,

  Month(DateNumber) AS Month,

  MonthName(DateNumber) AS MonthYear,

  'Q' &Ceil(Month(DateNumber)/3) AS Quarter,

  'S' &Ceil(Month(DateNumber)/6) AS Semester,

  Year(DateNumber) AS Year,

  If(WeekDay(Date(DateNumber)) <> 6 and not match(Date(DateNumber),$(vPublicHolidays)),1,0) as WorkingDayFlag

  RESIDENT TempCalendar

  ORDER BY DateNumber ASC

  ;

  left join(Calendar)

  Load

  MonthYear,

  sum(WorkingDayFlag)      as TotalWorkingDays

  resident Calendar group by MonthYear;

  left join(Calendar)

  Load

  Date,

  MonthYear,

  if(MonthYear=peek(MonthYear), peek(WorkingDay) + WorkingDayFlag, WorkingDayFlag) as WorkingDay

  resident Calendar

  ORDER BY Date ASC

  ;

  DROP TABLE TempCalendar,HolidayTmp;

Regards

Stan