10 Replies Latest reply on Mar 13, 2019 10:05 AM by mark parker

Calculate the amount of weeks in a month

Hi all !

I am stuck when trying to calculate the amount of weeks in months over the years.

For example, I would like something like this :

Month, Year ofNumber of weeks
March 20164
April 20165
......

It would help me a lot,

Thank you very much !

• 1. Re: Calculate the amount of weeks in a month

What exactly are you basing this on? March has 31 days and April has 30 days, so where do 4 weeks and 5 weeks come from? Is this something related to a fiscal calendar?

• 2. Re: Calculate the amount of weeks in a month

Hi Ken,

Those were random numbers of week chosen by myself just to illustrate what I would like.

• 3. Re: Calculate the amount of weeks in a month

But what are you trying to calculate exactly? Every month has at least 4 weeks, but less than 5 weeks. How do you want to determine the number of weeks in a month?

• 4. Re: Calculate the amount of weeks in a month

Hi Marc,

I tried doing this, couldn't get through it all but here is what I did. Maybe someone can finish it.

Drag it to Text in Mark card and Quick table calculation: Difference

Then edit the table calculation to restrict it to monthly level.

Now this removes the first month for each year because there is no previous value for tableau to subtract from. If in your case it's January then I won't worry about it since January always have 4 weeks. If not, then it will need some work my friend.

Happy Vizing!

PS Result:

• 5. Re: Calculate the amount of weeks in a month

Hi,

I think you could use the following approach:

I created a calculation as :{FIXED year([Order Date]), month([Order Date]):COUNTD(DATEPART('week',[Order Date]))}

It counts the distinct number of weeks in a given month in a year.

Below is the screenshot:

2 of 3 people found this helpful
• 6. Re: Calculate the amount of weeks in a month

After seeing some of the other responses, I think I understand now. I'd suggest the following:

• Create a calculated field that gets the first date of the month.
• Create another calculated field that gets the first date of the next month.
• Create calculated fields that get the week number from each of the above dates.
• Do the math to calculate the weeks.

See the attached workbook.

1 of 1 people found this helpful
• 7. Re: Calculate the amount of weeks in a month

It really helped me resolve my problem.

Have a great day !

• 8. Re: Calculate the amount of weeks in a month

Good Work Anisha!!!

• 9. Re: Calculate the amount of weeks in a month

Thank you Deepak Rai

• 10. Re: Calculate the amount of weeks in a month

Based on what I am seeing, your calc is greater than 52 weeks in one year....meaning, its not accurate.