topic How to identify first sunday in a month in QlikView

How to identify first sunday in a month

Anonymous — Wed, 04 Feb 2015 19:35:48 GMT

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

Re: How to identify first sunday in a month

rwunderlich — Wed, 04 Feb 2015 20:12:56 GMT

I think this would work:

Date(

MonthStart(today())

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

)

Just replace both today() with your date field.

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: How to identify first sunday in a month

Frank_Hartmann — Wed, 04 Feb 2015 20:13:48 GMT

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 vDateMax = Floor(YearEnd(AddMonths(Today(), 1)));
LET vDateToday = Num(Today());

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

MasterCalendar:
LOAD
(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

Re: How to identify first sunday in a month

Wed, 04 Feb 2015 20:44:08 GMT

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.

Re: How to identify first sunday in a month

shree909 — Wed, 04 Feb 2015 21:30:29 GMT

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)

Hope this would be helpful.

Thanks

Re: How to identify first sunday in a month

elie_issa — Thu, 17 Nov 2016 09:41:07 GMT

So What should i change in the above to get the first Thursday in a month ?

Thanks.