Running stats for Last Month

[pflakes]

I'd like to run stats retrieving only Last Month's data using broadcast agent and run it on the first of the month. What I've come up with below works until January. In January 2006, it's going to look for December 2006. Is there some way to query for only last month's data? I'm struggling with the limited number date functions available.

= MonthNumberOfYear(<mydate>) = MonthNumberOfYear(CurrentDate()) -1 And Year(<mydate>)=Year(CurrentDate())

 

[tommyboyau]

Personally, I beleive that the best way to achieve something like this is to develop a pre-defined condition in the universe.
Speak to your developer or if you are the developer even better!

Using a pre-defined condition allows you to use the database functions as well. If you use oracle db for example , have a look at the datediff function.

Hope that sheds some light on the situation. If you are able to make a predefined condition let me know what type of database you are running this on and I might be able to provide a bit more help.

 

[kskid]

If you are just looking for last month's data then you could use either

{Table.date} in LastFullMonth

or

{teble.date} in 
[dateserial(year(currentdate),month(currentdate)-1,1) to
dateserial(year(currentdate),month(currentdate),1-1)]

The dateserial function automatically adjusts for change of year.

-lw

 

[blom0344]

kskid,

Aren't you referencing Crystal Reports instead of BO?

Ties Blom
Information analyst

 

[pflakes]

tommyboyau -

Thanks for the help. I've worked it out with our developpers and we've got it up and running! We added an object called LAST MONTH which is defined as:

DateAdd(m,-1,Convert(varchar(8),GetDate(),112))-Day(GetDate())+1

(SQL Server Database)