topic Re: Count the number of saturdays & sundays between two dates in QlikView

Count the number of saturdays & sundays between two dates

Fri, 22 Mar 2013 08:09:30 GMT

Hi folks,

I've a small issue here. I'm calculating the count of saturdays and sundays between two dates. I've generated master calendar iun my qvw. I used the following expression to calculate the desired:

=count({1<CalendarWeek={Sat,Sun},CalendarDate={">=$(=Date#('16-03-2013','DD-MM-YYYY'))<=$(=Date#('31-10-2014','DD-MM-YYYY'))"}>}CalendarDate)

which is returning zero.

Also I've used the expression =Count({1<CalendarDate={"<=$(=date#('31/10/2014','DD/MM/YYYY'))>=$(=date#('16/03/2013','DD/MM/YYYY'))"}>}CalendarMonth)

to calculate the number of months between two dates, which is returning 595(no way near the result.

can you guys let me know where I'm going wrong. I've attached my qvw as well.

Thanks in advance

Surendra

Re: Count the number of saturdays & sundays between two dates

Anonymous — Fri, 22 Mar 2013 08:30:40 GMT

Your second expression would count the number of days between the dates. There is apparently one CalendarMonth value for each date so 31 times Jan for january etc.

I would suggest creating a flag in your calendar where you flag all Saturdays and Sundays as 1 under a field called WeekendFlag for example. Then you can just sum up the field WeekendFlag for your date range.

Re: Count the number of saturdays & sundays between two dates

Anonymous — Fri, 22 Mar 2013 08:38:24 GMT

Yes Johannes is correct,

you can do it by using this expression

in load script.

if (weekday(yourdatefield)='Sat' or weekday(yourdatefield)='Sun' , 'A') AS Weekendflag

then use this expression count(Weekendflag)

Regards,

anant

Re: Count the number of saturdays & sundays between two dates

nirav_bhimani — Fri, 22 Mar 2013 08:42:24 GMT

HI,

Try this Expression

=count({1<CalendarWeek={'Sat','Sun'},CalendarDate={">=$(=Date#('01-01-2000','DD/MM/YYYY'))<=$(=Date#('31/01/2000','DD-MM-YYYY'))"}>}CalendarDate)

It will display correct result for that month.

Now replace those date with your date .

Regards,

Nirav Bhimani

Re: Count the number of saturdays & sundays between two dates

Fri, 22 Mar 2013 08:59:41 GMT

Hi All,

Thanks for your quick response.

@Johannes,anantmaxx :

I've created the weekend flag as you said. But when I use the expression:

=sum({1<CalendarDate={">=$(=date#('16-03-2013','DD-MM-YYYY'))<=$(=Date#('31-10-2014','DD-MM-YYYY'))"}>}Weekendflag) in the front end it's not working. There is no error in the script and the 'Weekendflag' field is displaying 1 for all saturdays and sundays.

@Nirav:

It's working absolutely fine with date range you've taken.But when I enter my date fields it's displaying wrong value.

=count({1<CalendarWeek={'Sat','Sun'},CalendarDate={">=$(=Date#('01-01-2013','DD/MM/YYYY'))<=$(=Date#('31/12/2014','DD-MM-YYYY'))"}>}CalendarDate).

Seems like a problem with date interpretation.

Because when I enter year range instead of date range , it's showing correct result for the above expressions , we've discussed.

Re: Count the number of saturdays & sundays between two dates

nirav_bhimani — Fri, 22 Mar 2013 09:23:51 GMT

Hi,

try Following Expression,

if (weekday(yourdatefield)='Sat' or weekday(yourdatefield)='Sun' , 1, 0) AS Weekendflag

=sum({1< Weekendflag={1} ,CalendarDate={">=$(=date#('16-03-2013','DD-MM-YYYY'))<=$(=Date#('31-10-2014','DD-MM-YYYY'))"}>}Weekendflag)

Regards,

Nirav Bhimani

Re: Count the number of saturdays & sundays between two dates

Fri, 22 Mar 2013 09:27:07 GMT

Hi Nirav,

That is returning zero.

Re: Count the number of saturdays & sundays between two dates

Fri, 07 Nov 2014 02:41:08 GMT

Try the following Formula

The Following will give you count of all Mondays

sum(

if(

mod((leaveTo-leaveFrom),7)

if(

(weekday(leaveTo)-/* Change for day of week */0)<0,

weekday(leaveTo)-/* Change for day of week */0+7,

weekday(leaveTo)-/* Change for day of week */0)

,floor((leaveTo-leaveFrom)/7)+1

,floor((leaveTo-leaveFrom)/7))

)