topic Re: Exclude weekends for calculating data in QlikView

Exclude weekends for calculating data

arpita — Thu, 11 Mar 2021 15:36:37 GMT

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)

Re: Exclude weekends for calculating data

MayilVahanan — Fri, 12 Mar 2021 04:36:28 GMT

Hi Arpita,

Bring new field weekday from script & try like below

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

Re: Exclude weekends for calculating data

Vegar — Fri, 12 Mar 2021 05:40:10 GMT

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)

Re: Exclude weekends for calculating data

arpita — Fri, 12 Mar 2021 07:45:44 GMT

Hi Mayil and Vegar,

Both of these doesnt work

Re: Exclude weekends for calculating data

Vegar — Fri, 12 Mar 2021 08:37:53 GMT

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)

Re: Exclude weekends for calculating data

arpita — Fri, 12 Mar 2021 11:37:19 GMT

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)