topic Re: How to count # of Mondays, Tuesdays etc in any given calendar month. in QlikView

How to count # of Mondays, Tuesdays etc in any given calendar month.

Mon, 08 Apr 2013 18:57:11 GMT

Is there an easy way to count up the number of each specific named day in a calendar month? I need to be able to multiply certain numbers by say the number of wednesdays in a month..

Thanks,

Stan

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Anonymous — Mon, 08 Apr 2013 19:56:15 GMT

Stan,

This will work in the script, assuming you have table Calendar with Month, Year, and Date fields:

JOIN (Calendar) LOAD
Month,
Year,
sum(if(weekday(Date)=0,1,0) as "# Mondays",
sum(if(weekday(Date)=1,1,0) as "# Tuesdays",
...
sum(if(weekday(Date)=6,1,0) as "# Sundays"
RESIDENT Calendar
GROUP BY Month,Year;

Regards,
Michael

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Wed, 10 Apr 2013 20:11:34 GMT

Hi Michael,

First off, thanks for the response.

I actually don't have a standalone calendar like that. I'm loading a calendar out of my data warehouse as follows:

SurgeryStartDate:

replace LOAD

date("Surgery Date",'MM/DD/YYYY') as "Surgery Date",
num("Surgery Start Date CY Year") as "Surgery Start Date CY Year",
"Surgery Start Date CY Qtr",
"Surgery Start Date CY Month",
num("Surgery Start Date FY Year") as "Surgery Start Date FY Year",
"Surgery Start Date FY Qtr",
"Surgery Start Date FY Month",
"Surgery Start Weekday",
"Surgery Start Month"
where exists ([Surgery Date])
;
SQL

SELECT
D.FULL_DT AS "Surgery Date",
D.C_YYYY AS "Surgery Start Date CY Year",
SUBSTR(D.CY_YYYY_QTR,3,8) AS "Surgery Start Date CY Qtr",
SUBSTR(D.CY_YYYY_MM_NM,3,9) AS "Surgery Start Date CY Month",
D.F_YYYY AS "Surgery Start Date FY Year",
SUBSTR(D.FY_YYYY_QTR,3,8) AS "Surgery Start Date FY Qtr",
SUBSTR(D.FY_YYYY_MM_NM,3,9) AS "Surgery Start Date FY Month",
SUBSTR(D.DAY_NM,0,3) AS "Surgery Start Weekday",
SUBSTR(D.MONTH_NM,0,3) AS "Surgery Start Month"
FROM CDW.MASTER_DATE D
JOIN (SELECT MIN(CONTACT_DT_KEY) AS MIN_DT ,MAX(CONTACT_DT_KEY) AS MAX_DT FROM CDW.VISIT WHERE CONTACT_DT_KEY >= 20090701 AND CONTACT_DT_KEY <> 99991231) X1
ON D.DT_KEY BETWEEN X1.MIN_DT AND X1.MAX_DT;

So, being a total rookie to this, how would I incorporate your load statement in to that. I can't just call the SurgeryStartdate that I'm using for my load statement can I?

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Anonymous — Thu, 11 Apr 2013 01:15:38 GMT

Well, the table SurgeryStartDate is your Calendar, and you have Date, Month, and Year, just named differently. Use my logic, replace with your field name and table name, and it will work.

Regards,

Michael

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

swuehl — Thu, 11 Apr 2013 08:06:26 GMT

You might have less Surgery Dates than calendar dates, so just using the SurgeryStartDate table might miss some weekdays when doing the weekday aggregation in the join.

I think you should prepare a master calendar (there are hundreds of threads, design blogs here in the forum etc how to do this) based on your surgery date field, which contains all calendar dates. Then use this master calendar to create your number-of-weekdays-in-a-month field like suggested above.

You can also change the logic a little to perform a GROUP BY Year, Month, Weekday and do a count of weekdays and set the result into a common field (WeekdaysInYearMonth or similar) which could make it easier to retrieve the number afterwards.

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Thu, 11 Apr 2013 17:04:11 GMT

The SQL thats pulling together my surgerydate table actually pulls it from my master_date table, which has EVERY date up to like 2029 right now. Maybe I can just count it in the SQL itself instead.. hrm

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Thu, 11 Apr 2013 17:04:20 GMT

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Thu, 11 Apr 2013 17:55:42 GMT

Hi ,

Can you try the below code.

Here 'Date' is the Date field which can be any Date so that you can get the Number of Mondays in that month .

If you want to calculate the Tue ,Wed ,Thu , Fri , Sat or Sun

just past instead of 'Mon' in the below code

Floor((MonthEnd (Date) - MonthStart(Date) )/7 ) +if(WeekDay( WeekStart(MonthEnd(Date)))='Mon' ,1,0)

If there is anything please let me know .

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Fri, 12 Apr 2013 19:24:59 GMT

That was a good stab, but its not calculating correctly. I set a variable to equal addmonths(today),-1) and save that to vPriorMonth, which for today's date saves 3/12/13 (since today is 4/12/13).

From there I applied your formula as is, substituting my variable for the Date and it counted that March of 2013 had 5 Mondays, and if set to 'Fri' it says there are 4 Fridays. When looking at a physical calendar its 4 mondays, 5 fridays. Perhaps its not liking the functions in the variable to force it to prior month?

April 2013 = 4 Sun, 5 Mon, 5 Tue, 4 Wed, 4 Thu, 4 Fri, 4 Sat

March 2013 = 5 Sun, 4 Mon, 4 Tue, 4 Wed, 4 Thu, 5 Fri, 5 Sat

It's like so close and easy to drop right in to variables... just math is slightly off unless I missed something.

I even went as far as throwing the functions of my variable in to the full function:

=floor((monthend(addmonths(today(), -1))-monthstart(addmonths(today(), -1)))/7)+if(weekday(weekstart(monthend(addmonths(today

(), -1))))='Fri', 1, 0)

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Fri, 12 Apr 2013 21:02:16 GMT

HI ,

First use this Set statement in your Script

Set DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Then you the below code .

Floor((MonthEnd (Today()) - MonthStart(Today()) )/7 ) +

if( num( WeekDay(MonthStart(Today())))+1<= Match('Mon', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) and Match('Mon', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) <=num( WeekDay(MonthEnd(Today())))+1 ,1,0)

So if you want to count Tuesday then Replace 'Mon' by 'Tue' at 2 places as you can see above .

Use only strings among these Mon,Tue,Wed,Thu,Fri,Sat,Sun

Please reply me for the same .

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

Mon, 15 Apr 2013 14:59:13 GMT

This last iteration worked beautifully.. thank you!!

Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

NITIN_GUPTA — Mon, 03 Jun 2019 16:17:07 GMT

The solution is not working properly