Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpita
Contributor III
Contributor III

Exclude weekends for calculating data

Hi

Load

SET_ID,

AGE,

BREAK,

makedate(subfield(FileBaseName(), '_', 7), subfield(FileBaseName(), '_', 6), subfield(FileBaseName(), '_', 5)) as Date

FROM
$(file)

I have used the below expression to find the difference in Count(SET_ID) between a day and its previous day. It works fine for Tuesday till Friday but for the Monday's data, its doesnt show the difference because of the falling weekends. Please help

=Count({<Date={'$(=max(Date))'}>}total SET_ID)-Count({<Date={'$(=date(max(Date)-1))'}>}total SET_ID)

1 Solution

Accepted Solutions
Vegar
MVP
MVP

The count of SET_ID for the latest non-weekend date in your selection.

Count({< Date={'$(=date(max({<Date={"<=$(=max(Date))"},[WeekDay]={"<5"}>}Date,1)))'}>}total SET_ID)

The count of SET_ID for the second latest non-weekend date in your selection.

Count({<Date={'$(=date(max({<Date={"<=$(=max(Date))"},[WeekDay]={"<5"}>}Date,2)))'}>}total SET_ID)

 

Vegar_0-1615538085932.png

 

View solution in original post

5 Replies
MayilVahanan

Hi Arpita,

Bring new field weekday from script & try like below

=date(max({<WeekDay-={'Sun','Sat'},Date=>}Date)-1)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Vegar
MVP
MVP

Hi , I believe that @MayilVahanan  solution still will return Sundays values on a Monday. Please correct me if I am wrong.

I would re-use that approach, but use the second parameter of max()  to fetch the correct previous date. 

Count({<Date={'$(=date(max({< [WeekDay]-={"Sat","Sun"}>}Date,2)))'}>}total SET_ID)

arpita
Contributor III
Contributor III
Author

Hi Mayil and Vegar,

Both of these doesnt work

Vegar
MVP
MVP

The count of SET_ID for the latest non-weekend date in your selection.

Count({< Date={'$(=date(max({<Date={"<=$(=max(Date))"},[WeekDay]={"<5"}>}Date,1)))'}>}total SET_ID)

The count of SET_ID for the second latest non-weekend date in your selection.

Count({<Date={'$(=date(max({<Date={"<=$(=max(Date))"},[WeekDay]={"<5"}>}Date,2)))'}>}total SET_ID)

 

Vegar_0-1615538085932.png

 

arpita
Contributor III
Contributor III
Author

Thanks Vegar....It worked 🙂

 

CAn you help me with this

 

I have a calculated field (CCY_SETID) in my load statement. Now I need to have the total count of (CCY_SETID)  in load script.

TLMMO:

LOAD
SET_ID,
IF(Wildmatch(SET_ID,'*GDS*')=1,NULL(),SET_ID) AS CCY_SETID

FROM
$(file)