Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How to identify first sunday in a month

How to identify first sunday every month .And you just have Date column with you . Thanks for your help in advance.

1 Solution

Accepted Solutions
Master II

try using a mastercalendar. then its easy!

LET vZeitraum12Monate = '=\$' & '(vAktuellerMonat)' & '-11';
LET vZeitraum6Monate = '=\$' & '(vAktuellerMonat)' & '-5';
LET vZeitraum3Monate = '=\$' & '(vAktuellerMonat)' & '-2';

LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateToday = Num(Today());

TempCalendar:
\$(vDateMin) + RowNo() - 1 AS DateNumber,
Date(\$(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE \$(vDateMin)+IterNo()-1<= \$(vDateMax);

MasterCalendar:
(
year(TempDate)*12 + Month(TempDate)) - (year(\$(vDateMin))*12 + Month(\$(vDateMin))) as MonthNumber,
(
year(TempDate)*12 + Month(TempDate)) - (year(\$(vDateToday))*12 + Month(\$(vDateToday))) as MonthDiff,
'AK_' &
Date(TempDate,'YYYYMMDD') AS Referal_Date,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Num(Month(TempDate),00) AS MonthAsNumber,
Year(TempDate) AS CalendarYear,
'Q' &
Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekDayAndYear,
Num(Week(TempDate),'00') & '-' & Year(TempDate) as CalendarWeekAndYear,
Date(TempDate,'YYYY-MM') AS CalendarMonthAndYear,
Num(Date(TempDate,'YYYYMMDD')) AS CalendarYearMonthDay,
Year(TempDate)&Num(Month(TempDate),00) AS CalendarYearMonth,
Num(Month(TempDate),00)&'/'&Date(TempDate,'YY') AS DateDimension,
Year2Date(TempDate, 0, 1, \$(vToday))*-1 AS CalendarCurYTDFlag,
Year2Date(TempDate,-1, 1, \$(vToday))*-1 AS CalendarLastYTDFlag,

if(WeekDay(TempDate)='So' and Day(TempDate)<='07','First_Sunday_in_month','') as First_Sunday_in_month
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

then you can use the field "First_Sunday_in_month"  and you will only see the expected values

5 Replies

I think this would work:

Date(

MonthStart(today())

+ (6 - weekday(MonthStart(today())))

)

Just replace both today() with your date field.

-Rob

http://robwunderlich.com

Master II

try using a mastercalendar. then its easy!

LET vZeitraum12Monate = '=\$' & '(vAktuellerMonat)' & '-11';
LET vZeitraum6Monate = '=\$' & '(vAktuellerMonat)' & '-5';
LET vZeitraum3Monate = '=\$' & '(vAktuellerMonat)' & '-2';

LET vDateMin = Num(MakeDate(2008,1,1));
LET vDateToday = Num(Today());

TempCalendar:
\$(vDateMin) + RowNo() - 1 AS DateNumber,
Date(\$(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE \$(vDateMin)+IterNo()-1<= \$(vDateMax);

MasterCalendar:
(
year(TempDate)*12 + Month(TempDate)) - (year(\$(vDateMin))*12 + Month(\$(vDateMin))) as MonthNumber,
(
year(TempDate)*12 + Month(TempDate)) - (year(\$(vDateToday))*12 + Month(\$(vDateToday))) as MonthDiff,
'AK_' &
Date(TempDate,'YYYYMMDD') AS Referal_Date,
Day(TempDate) AS CalendarDay,
WeekDay(TempDate) AS CalendarWeekDay,
Week(TempDate) AS CalendarWeek,
Month(TempDate) AS CalendarMonth,
Num(Month(TempDate),00) AS MonthAsNumber,
Year(TempDate) AS CalendarYear,
'Q' &
Ceil(Month(TempDate)/3) AS CalendarQuarter,
WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekDayAndYear,
Num(Week(TempDate),'00') & '-' & Year(TempDate) as CalendarWeekAndYear,
Date(TempDate,'YYYY-MM') AS CalendarMonthAndYear,
Num(Date(TempDate,'YYYYMMDD')) AS CalendarYearMonthDay,
Year(TempDate)&Num(Month(TempDate),00) AS CalendarYearMonth,
Num(Month(TempDate),00)&'/'&Date(TempDate,'YY') AS DateDimension,
Year2Date(TempDate, 0, 1, \$(vToday))*-1 AS CalendarCurYTDFlag,
Year2Date(TempDate,-1, 1, \$(vToday))*-1 AS CalendarLastYTDFlag,

if(WeekDay(TempDate)='So' and Day(TempDate)<='07','First_Sunday_in_month','') as First_Sunday_in_month
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

then you can use the field "First_Sunday_in_month"  and you will only see the expected values

Not applicable
Author

And , instead of adding a field "First_Sunday_in_Month", calculate it by min() of id column for selected month, year and weekday=Sunday. This  will avoid adding another column if you require first Monday of the month next month.

Partner - Specialist II

Hi Vinay,

If use this  function

Date

(Weekend(monthstart(today(1))),'MM/DD/YYYY')

u will get only the first sunday of everymonth...

for testing the condition wiht the other dates

=

If((Floor(Date(Weekend(monthstart(today(1))),'MM/DD/YYYY'))=floor(Date(today(1)-3,'MM/DD/YYYY'))),1,0)

=

If((Floor(Date(Weekend(monthstart(today(1))),'MM/DD/YYYY'))=floor(Date(today(1),'MM/DD/YYYY'))),1,0)