7th Working date for each month

[agni_gold]

Hi Friends ,

In my below code  , i have flag for working day and non working day , but i also want one column having 7th working date for each month

LET vWeeklyHolidays = 'Sat;Sun';

TempDate:

LOAD

YearStart(Today()) + RowNo() - 1 AS TempDate

Autogenerate(365);

CalendarMaster_Temp:

LOAD

    Date(TempDate) AS TempDate,

    Year(TempDate) AS Year,

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

    Month(TempDate) As Month,

    Day(TempDate) As Day,

    WeekDay(TempDate) AS WeekDay,   

    Week(TempDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS TempDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(TempDate) AS MinDate,

    Max(TempDate) AS MaxDate

RESIDENT TempDate;

LEFT JOIN(CalendarMaster_Temp)

LOAD Date(Holiday) AS TempDate,

     Description AS HolidayDescription,

     1 AS IsHolidayFlag

FROM

(ooxml, embedded labels, table is Holidays);

Calendar:

LOAD

  TempDate,

  TempDate  AS Date,

    Year,

    Quarter,   

    Month,

    Day,

    WeekDay,   

    Week,

    HolidayDescription,

  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(TempDate)) OR IsHolidayFlag = 1, 0, 1) AS IsHolidayFlag,

  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(TempDate)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType

Resident CalendarMaster_Temp;

DROP TABLE CalendarMaster_Temp;

DROP TABLE TempDate;        

[swuehl]

Maybe something like

LOAD

     TempDate,

     If( LastWorkDate(Monthstart(TempDate),7) =TempDate,1,0) as IsSeventhWorkingDayInMonth

You can add a list of holidays to LastWorkDate() function, if you want and need it.

[agni_gold]

please explain little more about  ,

You can add a list of holidays to LastWorkDate() function

how to do this ?

[agni_gold]

How can i add holidays , because i need 7th working day.

[aarkay29]

or you can try something like this

If(NetWorkDays(Monthstart(Date),Date,'05/01/2017','07/04/2017','12/25/2017')=7,Date) as IsSeventhWorkingDayInMonth

'05/01/2017','07/04/2017','12/25/2017'  are list of holidays!!

[swuehl]

Basically, it's explained in

lastworkdate ‒ QlikView

You can read your external list of holidays (excel sheet) in a QV table and aggregate the records to a comma separated list of values, finally stored to a variable.

It's demonstrated in

Calculating Holidays in script

[agni_gold]

Hi Friends ,

in my attached  , file may 2017 , 7th working day should be 11th May 2017.

Can you please see what doing wrong here .

[swuehl]

The variable should go into the LastWorkDay() function, not as argument to the If() function.

If( LastWorkDate(Monthstart(TempDate),7, $(vHolDays)) =TempDate,1,0) as IsSeventhWorkingDayInMonth

Also, the list of holidays should enclose each holiday in single quotes:

Holidays:

Load

concat(chr(39)&Holiday &chr(39),',') as Holidays

FROM

(ooxml, embedded labels, table is Holidays);

[agni_gold]

if i want same as first working date for month then can i use like

If(FirstWorkDate(Monthstart(TempDate),0, $(vHolDays)) =TempDate,1,0) as IsFirstWorkingDayInMonth

[swuehl]

No...

Why not just change 7 to 1?

If( LastWorkDate(Monthstart(TempDate),1, $(vHolDays)) =TempDate,1,0)