Weekly Rolling Count Help

[TJVFree]

Hi,

I’m trying to do a rolling count by week. Similar to the formula I have below.

I would really appreciate any help I can get in converting the monthly formula below to weekly.

*Also, if there is a way I can get the average Daily/Weekly/Monthly/Yearly

Reviewed10M: Sum(IIf([MyField] Between DateSerial(Year(Date()),Month(Date())-10,1) And DateSerial(Year(Date()),Month(Date())-9,0) And [MyField] Is Not Null,1,0))

Thank you for your time and help

Corey

 

[PWise]

sum(iif(Datediff("ww",mydatefield,SomeotherDate)=xxx,1,0))

 

[PWise]

Reviewed10M can also be done this way
sum(iif(Datediff("m",mydatefield,SomeotherDate)=xxx,1,0))

 

[TJVFree]

Thank you PWise. I'm not sure what I would put in the "Someotherdate or xxx

 

[PWise]

if I understand what you are trying to do with your expression is
if the date in myfield is 10 months ago add 1 to the count
DateSerial(Year(Date()),Month(Date())-10,1)
then you can use the datediff function
iif(datediff("m",mydatefield,dateadd("m",-10,date())=10,1,0)
if the diff in months between your date field and 10 months back is 10 add 1 if not add 0
for weeks
iif(Datediff("ww",mydatefield,dateadd("ww",-10,date()))=10,1,0)
if the diff in Weeks between your date field and 10 Weeks back is 10 add 1 if not add 0

 

[TJVFree]

Thanks PWise, That worked perfect