Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Weekday from start of the month

Hi,

I need to calculate in variable the number of Saturdays since the beginning of the month.

Example: Today is March 18.

R:   3   (Saturdays of the start of the month)

Any idea?


Thank you,
Nuno

6 Replies
Not applicable

Re: Weekday from start of the month

Hi,

LET StartDate = num(MonthStart(Today()));
LET EndDate = num(Monthend(Today()));

TEMP_Calendar:
Load *,
Year(Date) AS [Year],
Month(Date) AS [Month],
Ceil(MONTH(DATE(Date))/3) AS [Quarter],
DATE(Date) & WeekDay(Date) AS CALENDAR_WEEKDATE;
Load Date($(StartDate)+(Iterno()-1)) as Date
AUTOGENERATE 1 
WHILE $(StartDate)+IterNo()-1<= $(EndDate);

DatesInCurrentmonth:
LOAD
Date AS YEAR_DATE
RESIDENT TEMP_Calendar
WHERE RIGHT(CALENDAR_WEEKDATE,3) = 'Sat' AND RIGHT(CALENDAR_WEEKDATE,3) = 'Sun';

Working_days_passed_this_year:
LOAD
count(YEAR_DATE) as workingDaysPassedThisYear
RESIDENT DatesInCurrentmonth;

DROP TABLE DatesInCurrentmonth;

This is a genric example you have to modify accordingly.

If you want omly Sat count then you have to remove AND RIGHT(CALENDAR_WEEKDATE,3) = 'Sun'.

And this will give all the sat in the month, if want till today then use a where clause in count loading loke

where YEAR_DATE<today();

Hope it helps you.

Regards,

Yaseen

sushil353
Honored Contributor II

Re: Weekday from start of the month

hi Nuno

try this:

=if(weekday(MonthStart(DATEFIELD))='Mon',Round(((WeekStart(DATEFIELD,0,-2)-monthstart(DATEFIELD))/7)),Round(((WeekStart(DATEFIELD,0,-2)-monthstart(DATEFIELD))/7)+1))

HTH

Sushil

Not applicable

Re: Weekday from start of the month

Hi Sushil,

It's ok. Thanks.

It is possible no count holidays?

sushil353
Honored Contributor II

Re: Weekday from start of the month

Hello Nuno,

Please explain your requirment.

HTH

Sushil

Not applicable

Re: Weekday from start of the month

Hello,

If you count 3 Saturdays but one of them is holiday. Only return 2.

mov
Esteemed Contributor III

Re: Weekday from start of the month

Here is what I'd use:

=count(distinct if(weekday(Date)=5 and Date>=MonthStart(today()) and Date<=today()
     
and match(Date, H)=0               // this line excludes holidays
      ,
Date))

Where variable H contains a list of holidays, comma separated, like this:

'01/01/2013', '12/24/2013', '07/04/2013'

Regards,

Michael

Community Browser