
1. Re: formula required the total count of weekday in a month
Shinichiro Murakami Jul 7, 2016 10:14 PM (in response to Anh Ho)I cannot understand how to read your data, so I just created simple date calendar to explain date counting.
[YYYYMM] // specify the year and the month from date
year([Date])*100+month([Date])
[weekday] // get weekday
datepart('weekday',[Date])
Below formula only works if your data has whole set of days
[Count weekday in month] // count days in month excluding 1(Sunday) and 7(Saturday)
{fixed[YYYYMM] :countd(if ([weekday]<>1 and [weekday]<>7) then [Date] end)}
In case of which your data misses a part of days
[Last Sunday]
{fixed [YYYYMM]:max(if [weekday]=1 then [Date] end)}
[Last Saturday]
{fixed [YYYYMM]:max(if [weekday]=7 then [Date] end)}
[weekends]
int((day([Last Sunday])1)/7)+1
+
int((day([Last Saturday])1)/7)+1
[Day in Month]
day(datetrunc('month',dateadd('month',1,[Date]))1)
[Calc weekdays in month]
[Day in Month][weekends]
Thanks,
Shin
9.1 attached

count_weekends_9.1.twbx 97.1 KB


2. Re: formula required the total count of weekday in a month
Anh Ho Jul 7, 2016 10:45 PM (in response to Shinichiro Murakami)Shinichiro,
Thank you for replying. Let me explain the data set a bit further for you.
My goal is to see how busy each of the car park is during weekday by comparing the total turnaround time (minutes) for all cars parked in a given carpark in a month against total weekday minutes in that given month. For example,the total occupied minutes for car park no 1 (TB1) during weekday is 17,630 minutes, total available weekdays minutes in January is 31 days x 24 hrs x 60 minutes =44640 minutes.Then I can calculate:
Carpark no.1 (TB1) occupancy (%) = 17630/44640*100=39.49%
I want to be able to calculate and show that 39.49% in a chart.
I hope it make sense .

3. Re: formula required the total count of weekday in a month
Shinichiro Murakami Jul 8, 2016 8:28 AM (in response to Anh Ho)Oh, I took it too difficult. Weekday does not mean Monday  Friday.,
Anyways, I could not replicate your number exactly, but I hope you still can understand the concept of solutions.
[Days in Month]
day(datetrunc('month',dateadd('month',1,[Date]))1)
[Days in Month July]
min(day({fixed [Year],[Month]:max([Date])}),[Days in Month])
[Minutes in Month]
24*60*[Days in Month July]
[Utilization]
sum([Minutes])/attr([Minutes in Month])
Thanks,
Shin

count_weekends_rep_9.1.twbx 392.5 KB
