Report running on 1st of month for previous 2 months

[Kevinski]

Hi

Some time ago I asked for help with the formula to bring back 2 months worth of data.

bdreed35 kindly came back with:

{Orders.Order Date} in dateadd("m",-1,minimum(lastfullmonth)) to maximum(lastfullmonth)

but now what if I want to run the report on the 1st of each month (or in effect at any time in a month) and have the report return the previous 2 months data? I get errors if I try to alter the above formula the way I think it should be.....

 

[mbarron]

Try this one:

datediff("m",{Orders.Order Date},currentdate) in 1 to 2

Mike

 

[bdreed35]

Kevin:

What kind of errors are you getting? What I provided in the previous post should return everything from the previous 2 months. If you need some help understanding how it works, I would be more than happy to help.

~Brian

 

[Kevinski]

Hi Brian

Whats happening is that if I'm running the report now (Sept) I get Septembers and Augusts data but as I now need to run a whole bunch of reports on the first of the month and return the previous 2 months worth of data I'm a little confused.

So not so much an error as a small change needed taking into account when these reports will be run.

So run in Sept - return Aug/ July
run in Oct - return Sept/ Aug etc

 

[mbarron]

Did you try using the formula I posted?

Mike

 

[bdreed35]

That is exactly what my formula will give you.

Is what you posted exactly what you are using, or is it just what I posted in the other thread?
Can you post your exact record selection formula please?

Here is how mine works if it helps any:

first date: dateadd("m",-1,minimum(lastfullmonth))

The lastfullmonth function returns the first day and the last day of the previous calendar month as a range. I use the minimum function to return the first day. The dateadd function then subtracts one month from that date, which would end up being the first day of 2 months ago.

second date: maximum(lastfullmonth)

This returns the last day of the previous month based upon the lastfullmonth function.

This should always work for you unless you modified and it and it is giving you different results. Posting your selection formula would really help out.

~Brian

 

[mbarron]

In order to display the dates that would be fitlered, I altered bdreed35's formula to:

cdate(dateadd("m",-1,minimum(lastfullmonth))) &" to " & maximum(lastfullmonth)

and it returned:

07/01/2004 to 08/31/2004

which is what you are requesting.


Mike