# 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:
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:
Date AS YEAR_DATE
RESIDENT TEMP_Calendar
WHERE RIGHT(CALENDAR_WEEKDATE,3) = 'Sat' AND RIGHT(CALENDAR_WEEKDATE,3) = 'Sun';

Working_days_passed_this_year:
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

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?

Honored Contributor II

Hello Nuno,

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.

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