topic Re: 7th Working date for each month in QlikView

7th Working date for each month

agni_gold — Tue, 02 May 2017 12:53:46 GMT

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;

Re: 7th Working date for each month

swuehl — Tue, 02 May 2017 13:26:24 GMT

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.

Re: 7th Working date for each month

agni_gold — Tue, 02 May 2017 13:36:56 GMT

please explain little more about ,

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

how to do this ?

Re: 7th Working date for each month

agni_gold — Tue, 02 May 2017 13:43:33 GMT

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

Re: 7th Working date for each month

aarkay29 — Tue, 02 May 2017 13:47:24 GMT

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!!

Re: 7th Working date for each month

swuehl — Tue, 02 May 2017 14:23:10 GMT

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

Re: 7th Working date for each month

agni_gold — Tue, 02 May 2017 15:15:54 GMT

Hi Friends ,

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

Can you please see what doing wrong here .

Re: 7th Working date for each month

swuehl — Tue, 02 May 2017 15:26:30 GMT

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);

Re: 7th Working date for each month

agni_gold — Tue, 02 May 2017 20:27:45 GMT

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

Re: 7th Working date for each month

swuehl — Tue, 02 May 2017 20:47:21 GMT

No...

Why not just change 7 to 1?

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