Current Month and Prior Month Records selection

[Bennie47250]

I have a report that is pulling records using the last full month function. Everything is working fine, however now “management” has decided that they would like to see the prior month data as well. What would be the best way to pull records for the last 2 full months?

Thanks
Bennie

 

[Bennie47250]

I should add this is using CR ver 7.0

 

[Guest_imported]

Try using the month function. If you specify that....

month(yourdatefield) = month(currentdate) -1 or
month(yourdatefield) = month(currentdate) -2

....this should do it. The only other thing you will need to consider is the year, if the month = 1 or 2 then you will need to look at months 11 and/or 12 of the previous year. You could do something like this which will return 11 and 12 for the previous year if necessary.

x = your date field

if month(currentdate) = 1
then (year(x) = year(currentdate) -1 and month(x) = 11) or
(year(x) = year(currentdate) -1 and month(x) = 12)
else if month(currentdate) = 2
then (year(x) = year(currentdate) -1 and month(x) = 12) or
(year(x) = year(currentdate) and month(x) = 1)
else (month(currentdate) = month(x) - 2
and year(x)) = year(currentdate)) or
(month(currentdate) = month(x) - 1 and
year(x) = year(currentdate))

 

[Bennie47250]

Matt H

Thanks, I will give this a try.

 

[Bennie47250]

Matt H.

The formula is working except when your woring with the last else statement. Crystal formula editor pops up the error message that "the remaining text does not appear to be a part of the formula"

if month(currentdate) = 1
then (year({ATBAL02.JOBDAT}) = year(currentdate) -1 and month({ATBAL02.JOBDAT}) = 11) or
(year({ATBAL02.JOBDAT}) = year(currentdate) -1 and month({ATBAL02.JOBDAT}) = 12)
else if month(currentdate) = 2
then (year({ATBAL02.JOBDAT}) = year(currentdate) -1 and month({ATBAL02.JOBDAT}) = 12) or
(year({ATBAL02.JOBDAT}) = year(currentdate) and month({ATBAL02.JOBDAT}) = 1)
else (month(currentdate) = month({ATBAL02.JOBDAT}) - 2
and year({ATBAL02.JOBDAT})) = year(currentdate))errors out here or
(month(currentdate) = month({ATBAL02.JOBDAT}) - 1 and
year({ATBAL02.JOBDAT}) = year(currentdate))

Any thoughts on how to correct this?

thanks

 

[Bennie47250]

I have found the problem and corrected. The formula I'm using now is:

if month(currentdate) = 1
then (year(X) = year(currentdate) -1 and month(X) = 11) or
(year(X) = year(currentdate) -1 and month(X) = 12)

else if month(currentdate) = 2
then (year(X) = year(currentdate) -1 and month(X) = 12) or
(year(X) = year(currentdate) and month(X) = 1)

else if Month (CurrentDate)>2
then (Year(X) = year (CurrentDate) and month (X) = Month (CurrentDate)-1) or
(Year(X) = year (CurrentDate) and month (X) = Month (CurrentDate)-2)


Thanks for you help.