• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 690
  • Last Modified:

Last 6 Months only


I have a report which shows month as a number and years, along with other data as below

Year          Month         Total
2013         2                  1200
2013         6                  1400 etc.

I now want the report to show only the last 6 months worth of data, this includes the current month, so if ran today would show months 7,8,9,10,11,12 and 1

I cannot seem to do this using month numbers.

Any help would be appreciated
  • 2
1 Solution
You want this based on Current Date?

You say that you want the last 6 months, including the current month, but, technically, your example includes 7 months -- The current month, plus the previous 6 months.

 Does your data have Month and Year fields and no date field?  Just checking.  You said that your report shows the month and year, but we don't know where the report gets those values.

halifaxmanAuthor Commented:
Hi Sorry last 6 months plus current month (7 months in total)

Yes my data does not have have a date only month number and year, there is not date in the database I am reporting from

I am assuming that there is no future data (with dates after today) that needs to be excluded from the report.

 Basically, you need to check the month and year together, not just the month.  There are various ways that you could do that.  My concern is that, depending on how you do it, the record selection test may or may not be passed to the server.  If not, then the server will have to send all of the records to the report, and then the report will do the filtering.  If you have a lot of data, that could be a real problem.

 I think there's a good chance that a test like the one below would be passed to the server, because it's simple and doesn't rely on anything like a db function to convert the month and year fields into a date.  Just enter that as your record selection formula (use your field names, of course).

{Month_field} >= Month (DateAdd ("m", -6, CurrentDate)) and
{Year_field} = Year (DateAdd ("m", -6, CurrentDate))
) or
{Year_field} > Year (DateAdd ("m", -6, CurrentDate))

 The first part picks up anything between 6 months ago and the end of that year.  The second part picks up anything in the year following that (eg. this month).  Since you (presumably) don't have any future data to exclude, you can assume that anything in a year greater than the year 6 months ago should be included.  FWIW, if you did need to exclude future data, you could add a check on the month to the second half, to make sure that it was <= the current month.

 Today, for example, CR would translate the formula above to

{Month_field} >= 7 and
{Year_field} = 2013
) or
{Year_field} > 2013

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now