11 Replies Latest reply on Jul 8, 2016 6:09 AM by Luciano Vasconcelos

How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

Does anyone know how to calculate the total number of weekdays in a month? I have a generic date/time field in a data set with hundreds of dates and I need to figure out how to take the total records that happen on particular days of the month (Monday, Tuesday, Wednesday, etc) and divide them by the number of weekdays that happen in a month. For example, this month, July 2016, has 5 fridays and 5 saturdays but only 4 thursdays, etc. If I had 200 records that happened on fridays this month, how would I write an equation where tableau counts the number of fridays (or saturdays, etc) this month so I could then find the avg records per specific weekday (200/5)?

• 1. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

Try this:

SUM( If DATEPART('weekday',today()) = 6 then 1 Else 0 End )

6 is the weekday of friday.

1 of 1 people found this helpful
• 2. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

Matthew, you also have the option of selecting the specific weekday in the by selecting the More option under day in the Date pill drop-down menu and selecting Weekday. You can also use a month filter to differentiate or look at the timeframe as a whole. Not sure if this is 100% what you were looking for, but hopefully this saved you some hassle!

• 3. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

This is moving in the right direction, however, this calculation is simply counting the records happening on the specific days of the week. So, for example, it calculates the total records that happen on any specified day, monday, tuesday, wednesday, etc. I need that but I mainly need to find out how many Mondays, Tuesdays, Wednesdays, etc there are in that particular month. I would essentially be using the equation you provided and divide it by the number of Mondays, or Tuesdays, etc in a month to get the avg number of cases per that weekday.

• 4. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

My formula gave you that. It's just use it do divide and use month as filter or column/row

• 5. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

I think this is what you are looking for now, thanks for clarifying. The calculation is: COUNT(DATEPART('weekday',[Date]))

Let me know if that helps.

1 of 1 people found this helpful
• 6. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

Unfortunately, the numbers I get, when setting up my sheet the same way are wildly different. I suspect this is because I'm working with thousands of records and it's allocating a 1 per each record for this calculation in the backend (if you look at the raw data). This works if I only had one record per each day of the week but because I have multiple it's simply totaling up the records per each day and displaying them. I think this may need to be solved with a level of detail expression I'm just not sure how to craft it. The issue is tableau aggregates everything row by row, so unless the equation is written with a {fixed} calculation I'm not sure how you'd calculate this otherwise.

• 7. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

It's just use COUNTD instead Count

• 8. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

It appears the solution was to use the equation below to find the total of how many weekdays were in a month.

{ FIXED [Facility],[Ca Dateserv]: COUNTD(DATEPART('weekday',[Ca Dateserv]))}

Facility is listed because I've segregated records by each facility and Ca Dateserve is my date field.

However I still need to figure out how to get the total records from each of those weekdays divided by the number of weekdays using a Level of detail equation.

1 of 1 people found this helpful
• 9. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

Can't you post a twbx file?

• 10. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

Not easily I can't. The data in the workbook is protected by HIPAA as it's healthcare data. I would need to make a test workbook with sample data and that would take a while. Plus I figured out how to solve my issue with the below equation...

sum([Number of Records]) / SUM({ FIXED [Facility],[Ca Dateserv]: COUNTD(DATEPART('weekday',[Ca Dateserv]))})

2 of 2 people found this helpful
• 11. Re: How to calculate number of weekdays (Mondays, Tuesdays, etc) in a month?

That's nice man.