Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

Store working days between begin and end dates in seperate variables

Hi folks ( stalwar1‌)

I have some questions and hope u'll help me solving them.

Here we go :

Imagine I have 2 dates:

BeginDate and EndDate

I want to have all the Dates between this Begin and EndDate that are Working Days and store each day in a variable:

Example:

BeginDate : 31/01/2018

EndDate : 06/02/2018


List of working days btwn these 2 days:

31/01/2018, 01/02/2018 , 02/02/2018, 05/02/2018, 06/02/2018

Thus, I want to have sthing like this:

vD1: 31/01/2018

vD2: 01/02/2018

vD3 : 02/02/2018

vD4: 05/02/2018

vD5: 06/02/2018


Is this possible? If so, any idea in how can I do such a thing?


1 Solution

Accepted Solutions
MVP
MVP

Re: Store working days between begin and end dates in seperate variables

May be this

Table:

LOAD Date

Where not Match(WeekDay, 'Sat', 'Sun');

LOAD Date(BeginDate + IterNo() - 1) as Date,

WeekDay(BeginDate + IterNo() - 1) as WeekDay

While BeginDate + IterNo() - 1 <= EndDate;

LOAD * INLINE [

    BeginDate, EndDate

    31/01/2018, 06/02/2018

];


FOR i = 1 to FieldValueCount('Date')

LET vD$(i) = FieldValue('Date', $(i));

NEXT;

5 Replies
MVP
MVP

Re: Store working days between begin and end dates in seperate variables

May be this

Table:

LOAD Date

Where not Match(WeekDay, 'Sat', 'Sun');

LOAD Date(BeginDate + IterNo() - 1) as Date,

WeekDay(BeginDate + IterNo() - 1) as WeekDay

While BeginDate + IterNo() - 1 <= EndDate;

LOAD * INLINE [

    BeginDate, EndDate

    31/01/2018, 06/02/2018

];


FOR i = 1 to FieldValueCount('Date')

LET vD$(i) = FieldValue('Date', $(i));

NEXT;

OmarBenSalem
Esteemed Contributor

Re: Store working days between begin and end dates in seperate variables

I'll always learn sthing from u:

I've done this:

Let varMinDate = num(date#('30/01/2018'));

Let varMaxDate = num(date#('06/02/2018'));

 

TempCalendar: 

LOAD 

              $(varMinDate) + Iterno()-1 As Num, 

              Date($(varMinDate) + IterNo() - 1) as TempDate 

              AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

             

              Dates: 

Load 

              rowno() as row, TempDate AS Date

             

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

             

             

for a=0 to FieldValueCount('row')-1

let Day$(a) = Peek('Date',$(a), 'Dates');

next a;


But didn't know how to exclude weekends !

OmarBenSalem
Esteemed Contributor

Re: Store working days between begin and end dates in seperate variables

I've tried ur solution Sunny, but I see that there variable created even for Staurday and sunday (02/02/2018 and 03/02/2018)

Capture.PNG

MVP
MVP

Re: Store working days between begin and end dates in seperate variables

Did you add this?

Table:

LOAD Date

Where not Match(WeekDay, 'Sat', 'Sun');

LOAD Date(BeginDate + IterNo() - 1) as Date,

WeekDay(BeginDate + IterNo() - 1) as WeekDay

While BeginDate + IterNo() - 1 <= EndDate;

LOAD * INLINE [

    BeginDate, EndDate

    31/01/2018, 06/02/2018

];


FOR i = 1 to FieldValueCount('Date')

LET vD$(i) = FieldValue('Date', $(i));

NEXT;

OmarBenSalem
Esteemed Contributor

Re: Store working days between begin and end dates in seperate variables

My bad.. WeekDays are written in french by default for me.. that's why !

Community Browser