Weekday from start of the month

[#1]

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

[#2]

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]

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

[#4]

Hi Sushil,

It's ok. Thanks.

It is possible no count holidays?

[sushil353]

Hello Nuno,

Please explain your requirment.

HTH

Sushil

[#6]

Hello,

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

[Anonymous]

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