Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
agni_gold
Specialist III
Specialist III

7th Working date for each month

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;        

9 Replies
swuehl
MVP
MVP

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
Specialist III
Specialist III
Author

please explain little more about  ,

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


how to do this ?

agni_gold
Specialist III
Specialist III
Author

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

aarkay29
Specialist
Specialist

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

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
Specialist III
Specialist III
Author

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

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
Specialist III
Specialist III
Author

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

No...

Why not just change 7 to 1?

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