Counting by weeks of the year

[apocunningham]

I really appreciate all the assistance I've received from the Community, I've learned alot. Thank you!

I do have another question that I could use some assistance with. I'm creating a Report Template to count the number of Investigations completed in a given county in a given week of the year. I'm struggling with the expression and steps. My current expression will count the number of Investigations within a 7 day period (<<Count(Select(Court[ID],AND([CourtDate]>=Today()-7,[CountyID]="Illinois - Alexander County")))>>), but does not help with creating a report that would represent all 52 weeks in a year. I've looked into WEEKNUM() and I'm confused on how to incorporate this to accurately get 52 weeks and how to edit my existing expression. 

I'm envisioning something like this in my report template:

County	Week 1	Week 2	Week 3	Week 4	Week 5
White County	3	6	4

Your assistance is much appreciated!

Thank you!

[WillowMobileSys]

The WEEKNUM() function reports weeks as calendar rows - even if partial rows.  See images below.

Also, note that a year is not precisely 52 weeks worth of days.  So there will always be 53 weeks represented by the WEEKNUM() function. 

You'll want to decide how you wish to deal with these partial weeks at the beginning and end of the year and determine if you want to even use WEEKNUM().  

If you want Week #1 to be a full 7 days always starting on Jan 1, then you cannot use WEEKNUM() as is.  You either need to apply an offset the get the week # you want - which varies from year to year.  Or you will need to build your own Week # counter.

First two Week rows in a 2022 Calendar

Last week row in a 2022 Calendar

[apocunningham]

Thanks for the explanation and this makes sense. It seems to be the most simple to live with and understand the 53 week per year which accounts for partial weeks in the beginning and end of the year. With that said, when I try to incorperate WEEKNUM() into my expression instead of Today() I keep getting error message, "Weeknum() is not used correctly". Any thoughts on this. I've never used this expression before. Thanks again for your help!

[WillowMobileSys]

Can you show the expression you are trying?   The WEEKNUM() function requires only a Date or DateTime type column/value.

[apocunningham]

Thanks for looking at this. Here is an example of my attempt below. In this example I was just trying to get this weeks count based on today's date.

<<Count(Select(Court[ID],AND(WEEKNUM([CourtDate]=Today(),[CountyID]="Illinois - Ogle County"))))>>

I'd rather create a table in my report template, see below:

County	Week 1	Week 2	Week 3	Week 4	Week 5
White County	3	6	4

In each column I would enter the expression for that week, i.e. WEEKNUM=1, WEEKNUM=2, etc... Is that possible am I track? Thanks for looking at juvenile attempt!

[WillowMobileSys · Accepted solution]

---

---

@apocunningham wrote:

<<Count(Select(Court[ID],AND(WEEKNUM([CourtDate]=Today(),[CountyID]="Illinois - Ogle County"))))>>

---

I believe your expression should be:

<<Count(Select(Court[ID],AND(WEEKNUM([CourtDate]) = 1,[CountyID]="Illinois - Ogle County")))>>

 You will need to replace 1 with each week number based on the column you are inserting it into.

[apocunningham]

Yep, that's it! Thank you so much for the assistance! This is a huge help!