help with monday of week X

[bdarch]

Please throw a fried brain a bone!

Background:

I have a summary field reporting total hours in a given week. I'd like the report to show the week number for the summarized records, plus the date of the monday the week started on "week beginning" would be the head of this date column.

Fields:

dDate: date field for each record

cDateWeekNumber = WeekOfYearFiscal(dDate;2)

cMondayofDateWeek = dDate-(DayOfWeek(dDate)-1)+1

Report:

subsummary on cDateWeekNumber(trailing)

The Problem:

sometimes, not always, the report will repeat a date in the "week beginning" column across two different week numbers - perplexing!

week beginning week

8.03 32

8.10 33

8.24 34

8.24 35

8.31 36

In this particular case, there *are* records from the week of 8.17, but as you can see - they don't come up in the subsummary.

Any thoughts about this perplexing case?

Thanks!

[comment]

Your cMondayofDateWeek calculation is incorrect - when dDate is a Sunday, it returns the following Monday - while WeekOfYearFiscal() still returns the preceding week.

Try instead:

date - DayOfWeek ( date - 1 ) + 1

BTW, you should group by this field, because WeekOfYearFiscal() will not sort correctly across years.