Select Expert Record. Last 6 months and week start date on Monday

[Former Member]

Hello,

i am trying to use the select expert record to grab the last 6 months worth of data. I require the week start day to be a monday and end on a sunday.

Can someone please provide some advice the best way to do this

kind regards

david

[abhilash_kumar · Accepted solution]

Hi David,

Use this as the Record Selection Formula to fetch last 6 month's data:

{Date_Field} >= dateadd('m',-6,currentdate)

In CR the first day of the week in Sunday, so, if the report is run today it'll give you records from June 1 to Nov 30. Let's say, for eg, Oct 1 (Saturday) and Oct 2 (Sunday) which are not the start days, instead Oct 3 is a Monday, so do you want to skip these two days?

-Abhilash

[Former Member]

I have managed to work it out by creating a formula

Formula: GroupWeekStartMon

-DayOfWeek (,crMonday )+1

and then grouping of this....

[Former Member]

Hello Abhilash,

Once again thanks for your reply.

Yes this is correct, when it goes back 6 months i need it to skip the days prior and begin on a monday as we are reporting on a mon-sunday basis.

I have tried your formula Date field >= dateadd('m',-6,currentdate) and it seems to grab 6 months of data, but the group weeks are not grouping from Monday to Sunday...

kind regards

david

Edited by: davitali on Dec 2, 2011 9:35 AM