4 Replies Latest reply on Sep 2, 2018 4:51 AM by Apivai Chatpimolkul

# How to display week number by Month

I am trying to display counts by Month and then week number but when I get to February the week number does not reset.

Appreciate any help.

Thank you,

Kay

• ###### 1. Re: How to display week number by Month

Hi Kay,

You can try below solution:

calculate min week for each month:  {FIXED MONTH([Order Date]):MIN(DATEPART('week',[Order Date]))}

then use below calculation :

new week:

CASE DATEPART('week', [Order Date])

WHEN [min week] then "week1"

when [min week]+1 then "week2"

when [min week]+2 then "week3"

when [min week]+3 then "week4"

when [min week]+4 then "week5"

when [min week]+5 then "week6"

END

cheers

2 of 2 people found this helpful
• ###### 2. Re: How to display week number by Month

Thank you for your solution.  That worked!

• ###### 3. Re: How to display week number by Month

one small correction if you want to display year also on your report then change the first calculation to

:{FIXED YEAR([Order Date]),month([Order Date]):MIN(DATEPART('week',[Order Date]))}.

If this answer helped you, please mark is as correct so that this thread will be closed.

• ###### 4. Re: How to display week number by Month

I also have a problem like this and I found it works (But if the data have only second half of the month, it will compute to be week 1). However, could you please describe the logical thinking behind these calculation syntax as I am not familiar with the date function that much?