Holiday Working question

[abhaysingh]

I have one table in which Total holidays dates are present,

i want total working days to count which is Count(Total Days of Month) - Count(Holidays)

getting some glitch in output pls suggest.

pls find the attached sample data for holidays

[abhaysingh]

pls suggest

[abhaysingh]

Hi Jagan,

Pls suggest

[abhaysingh]

kindly revert

[asgardd2]

Change variables(vMinDate,vMaxDate) from my example to:

LET vMinDate = Floor(MonthStart(peek('minDate',0,'MinMaxDate')))-1;
LET vMaxDate = Floor(today()); //You can set this date manually

[sasikanth]

HI,

Pls. check the attachment

[varshavig12]

In editor 

Directory;

LOAD Plant,

     [Profit Centre],

     [Material Group],

     Holidays,

    Date(Date#(Holidays,'DD.MM.YYYY'),'MMM-DD-YYYY') as Holidays1,

    Month(Date(Date#(Holidays,'DD.MM.YYYY'),'MMM-DD-YYYY')) as Month1

FROM

[Copy of Final working off days.xls]

(biff, embedded labels, table is Sheet1$);

In main sheet:

add:

Holidays1

Month1

In textBox write the expression as :

=num((Day(MonthEnd(Month1)))-Day(MonthStart(Month1)))-count(DISTINCT(Holidays1))

Note:

Select month and you will get the desired output in the textbox.

Assuming Sunday is already there in the excel sheet.

[abhaysingh]

Hi Anton,

Thanks for ur great help, in this if i want to calculate total days of current month as well than what shud i need to do?

regards

Abhay

[asgardd2]

You can add in the end of script :

/**Add a new field countOfDaysInMonthAll**/
LEFT JOIN(Data)
LOAD
DISTINCT
Month,
Year,
DAY(MonthEnd(MakeDate(Year,Month,01))) as countOfDaysInMonthAll
Resident Data;

And then. field "countOfDaysInMonthAll" is a really count of total days in month.

[jagan]

HI,

Did you checked the expression which I given in my previous post?  It is working as expected.

Regards,

jagan.

[jagan]

Hi,

In the solution check Jan - 2016 has only 30 days but it should be 31.  Use the script below to fix this, applied bold to the changed portion

tempData:

LOAD Plant,

     [Profit Centre],

     [Material Group],

     Holidays

FROM

[Final working off days.xls]

(biff, embedded labels, table is Sheet1$);

//Calculate min and max date value

MinMaxDate:

LOAD

  Min(Holidays) as minDate,

  Max(Holidays) as maxDate

Resident tempData;

LET vMinDate = Floor(MonthStart(peek('minDate',0,'MinMaxDate')));

LET vMaxDate = Floor(MonthEnd(peek('maxDate',0,'MinMaxDate'))); //You can set this date manually

DROP TABLE MinMaxDate;

//Generate calendar table

let vPeriod = $(vMaxDate) - $(vMinDate) + 1 ; // период календаря

Data:

LOAD

  $(vMinDate) + RowNo() - 1 as Date,

  Month($(vMinDate) + RowNo() - 1) as Month,

  Year($(vMinDate) + RowNo() - 1) as Year

AutoGenerate ($(vPeriod));

LEFT JOIN(Data)

LOAD

  Distinct //drop duplicate values

  Plant,

    [Profit Centre],

    [Material Group],

    Holidays as Date,

    'X' as DayIsHoliday

Resident tempData;

DROP TABLE tempData;

//Generate table with count ofdays in month and join to main table

LEFT JOIN(Data)

LOAD

  Count(Distinct Date) as CountOfDaysInMonth,

  Month,

  Year

Resident Data

Group by

  Month,

  Year;

//Join Count of holidays

//If you do not want calculate count of days in Plant - [Profit Centre] - [Material Group] , maybe only Plant... change fields(do not remember change each fields in group by ...) in table downside:

LEFT JOIN(Data)

LOAD

  Count(Distinct Date) as CountOfDaysInMonthHoliday,

  Month,

  Year,

  Plant,

    [Profit Centre],

    [Material Group]

Resident Data

Where DayIsHoliday = 'X'

Group by

  Month,

  Year,

  Plant,

    [Profit Centre],

    [Material Group];

//Calculacte working days

LEFT JOIN(Data)

LOAD

  Distinct

  CountOfDaysInMonthHoliday,

  CountOfDaysInMonth,

  CountOfDaysInMonth - CountOfDaysInMonthHoliday as CountOfWorkingDays

Resident Data