Exclude only Public Holidays irrespective of Saturday Sunday

[raajaswin]

Hi all,

While Calculating Ageing between two dates can we exclude only specified holidays? (i.e) I need to exclude only the list of dates specified irrespective of saturday or sunday. when I use Network days and specify the list of holidays in a variable. networkdays automatically excldues saturday, sunday also. Pls suggest an alternate approach

[prat1507 · Accepted solution]

HI aswin, =Div(WEEKDAY(day('2017-08-31')-6)+1-date('2017-08-31')+date('2017-09-25'),7)

returns 4, which is correct, it is the no. of saturdays.

If you want to exclude only sundays and holidays, use

Networkdays() = your expression which excludes Sat,Sun,Holiday + my exp  which returns no. of Sats

so the above exp will only exclude holidays and sundays. I hope you understand now.

Regards
Pratyush

[Kushal_Chawda]

try this

Data:

LOAD * Inline [

Date1,Date2

01/09/2017,25/09/2017 ];

AllDates:

LOAD *,

    date(Date1+IterNo()-1) as Dates

Resident Data

While Date1+IterNo()-1<=Date2;

DROP Table Data;

Left Join(AllDates) // join holliday file, rename field as per the holiday field name

LOAD *,1 as IsHoliday Inline [

Dates

05/09/2017 ];

New:

NoConcatenate

LOAD *

Resident AllDates

where IsHoliday<>1 and WeekDay(Dates)<>'Sun';

DROP Table AllDates;

Final:

NoConcatenate

LOAD Date1,

    Date2,

    Count(Dates) as AgeingDays

Resident New

Group by Date1,Date2;

DROP Table New;

replace inline table with your actual data file