topic Re: Count of Saturday in whole month in QlikView

Count of Saturday in whole month

mohammadkhatimi — Mon, 04 Apr 2016 11:14:41 GMT

For Example,

April 2016 has 30 days and 5 Saturdays.

I want to count only those 5 Saturdays,even if they aren't passed.

For instance today is(4th Apr 2016) Still the count should be 5 Saturdays.

This should work for every month.

Regards,

Mohammad

Re: Count of Saturday in whole month

kaushiknsolanki — Mon, 04 Apr 2016 11:16:30 GMT

Hi,

You can create a flag.

Load if(WeekDay(Date) = 'Sat',1,0) as SatFlag

From xyz;

and then use the Sum(SatFlag) in chart.

Regards,

Kaushik Solanki

Re: Count of Saturday in whole month

sunny_talwar — Mon, 04 Apr 2016 11:16:51 GMT

Are you wanting to do this in the script or the front end?

Re: Count of Saturday in whole month

swuehl — Mon, 04 Apr 2016 11:32:54 GMT

You can use something like

=Div(WEEKDAY(StartDate-6)+1-StartDate+EndDate,7)

=Div(WEEKDAY(Monthstart(today())-6)+1-Monthstart(today())+Floor(MonthEnd(today())),7)

to count the number of saturdays in a date range (StartDate, EndDate)

Or use a master calendar with added Weekday Count data, like indicated above (but maybe with additional monthly aggregations).

Re: Count of Saturday in whole month

mohammadkhatimi — Mon, 11 Apr 2016 05:02:01 GMT

@Sunny in frontend...

I want Count of Satudary is 5...

Re: Count of Saturday in whole month

avinashelite — Mon, 11 Apr 2016 05:49:40 GMT

Try like this

Sum(If(weekDay(date_field)='Sat',1,0))

Re: Count of Saturday in whole month

mohammadkhatimi — Mon, 11 Apr 2016 06:57:32 GMT

sunindia‌

Please help me with this issue.

Re: Count of Saturday in whole month

swuehl — Mon, 11 Apr 2016 08:42:05 GMT

I'm not Sunny, but have you tried my expressions I've suggested above?

Re: Count of Saturday in whole month

sunny_talwar — Mon, 11 Apr 2016 10:25:47 GMT

I would seriously suggest you to try Stefan's provided solution. More often then not his solutions are the best you will find on the community. So I urge you to not disregard his responses.

If it still doesn't work, then I don't mind chipping in, but I doubt that's going to happen

Re: Count of Saturday in whole month

Mon, 11 Apr 2016 11:03:27 GMT

Hi Mohammad

If you want to calculate the number of Saturdays in a month(April), it all depends on if you have data for the rest of the month (ie. after the 4th Apr 2016).

The formula - 'Sum(If(weekDay(date_field)='Sat',1,0))' will work but, if you only have data up until the 4th Apr 2016 then the answer will be 1, if you have data for the whole of April 2016 it will be 5

Re: Count of Saturday in whole month

mohammadkhatimi — Tue, 12 Apr 2016 11:16:04 GMT

I dont have the data for the future saturdays that's the main problem.

Kindly help me with this issue... for the future saturdays only for the current month.

Re: Count of Saturday in whole month

mohammadkhatimi — Tue, 12 Apr 2016 11:18:30 GMT

I want to do this in the master calendar. My date is linked with Master Calendar, so the data wont be there for future dates.

So I want the solution which will help me to count the saturdays.. which are not present in the dates.

Re: Count of Saturday in whole month

swuehl — Tue, 12 Apr 2016 11:28:40 GMT

Ok, but could you clarify why my suggested expression using Div() is not working for you?

For example, if you are using a SalesDate field and what to get the number of saturdays in the month the SalesDate is located in, you should be able to use something like

=Div(WEEKDAY(Monthstart(SalesDate)-6)+1-Monthstart(SalesDate)+Floor(MonthEnd(SalesDate)),7)

[Used e.g. in an expression in a chart with dimension SalesDate, or in a text box with a single SalesDate selected. Use aggregation functions like Max() around SalesDate when there are multiple SalesDate in the record set of your expression's scope]

Maybe you can just post some sample records with your expected results to understand better what your issue is.

Re: Count of Saturday in whole month

Tue, 12 Apr 2016 11:57:41 GMT

You need to create the Future dates

It was answered in this previous post :

Past and future dates in the same table‌

Re: Count of Saturday in whole month

mohammadkhatimi — Tue, 12 Apr 2016 12:20:16 GMT

Thank you...

Re: Count of Saturday in whole month

cspindley — Wed, 22 Jan 2020 08:29:41 GMT

on behalf of the nhs thanQ for sharing for free ...