Calculate prior month using all weeks that began (Sunday) in that mont 1

[sarend]

I am creating a report in Crystal 10, that will run weekly (Sunday - Saturday) showing records that were opened in the prior week. In addition, the requester wants a monthly report showing records that were opened in the prior month. At first we created a formula using lastfullmonth function, however, this is not what they want. They want all weeks that began in that month.

For example, for the month of July 2007, this would include data for weeks begining July 1, July 8, July 15, July 22, and July 29. So the week of July 29 will contain data through August 4. How can a create a formula to pull this?

The report is currently grouped by week based on open date, as you can see below. Now I need a formula that will sum all the weeks that begin in the same month. Thanks!

12/31/2006 3
1/7/2007 4
1/14/2007 6
1/21/2007 5
1/28/2007 11
2/4/2007 10
2/11/2007 15
2/18/2007 3
2/25/2007 5
3/4/2007 9
3/11/2007 10
3/18/2007 5
3/25/2007 12

 

[lbass]

Try something like:

if month({table.date}-dayofweek({table.date})+1) = month(currentdate-day(currentdate)) then {table.amt}

Insert summaries on this formula.

-LB

 

[sarend]

Thank you! That worked perfectly!