lastfullmonth with 31 day months

[khekking]

Greetings,

Running into a problem with the lastfullmonth. At the close of each month we run a report that selects data for the previous 12 months and categorizes sales. I am running into a problem with 31-day months where the 31st day isn't reporting. I'm using Crystal 11.

Sample for one catagory's sales for the lastfullmonth and it's previous month are below. Thanks in advance for your help.

if ({Invoice_Detail.INVDTE_32} in dateadd("m",0,minimum(lastfullmonth)) to dateadd("m",0,maximum(lastfullmonth))

AND {Invoice_Detail.PRTNUM_32} in ["00642-00", "00643-00", "00639-00", "00640-00", "00641-00", "00636-00", "00637-00", "00638-00", "00238-03", "00547-02", "01648-00", "01692-00", "01693-00", "01694-00", "00238-04", "00238-05", "00238-06"])

then (if{Invoice_Master.STYPE_31}="CR"
then({Invoice_Detail.INVQTY_32} * {Invoice_Detail.PRICE_32}) *-1
else{Invoice_Detail.INVQTY_32} * {Invoice_Detail.PRICE_32})

else 0

//////////

if ({Invoice_Detail.INVDTE_32} in dateadd("m",-1,minimum(lastfullmonth)) to dateadd("m",-1,maximum(lastfullmonth))

AND {Invoice_Detail.PRTNUM_32} in ["00642-00", "00643-00", "00639-00", "00640-00", "00641-00", "00636-00", "00637-00", "00638-00", "00238-03", "00547-02", "01648-00", "01692-00", "01693-00", "01694-00", "00238-04", "00238-05", "00238-06"])

then (if{Invoice_Master.STYPE_31}="CR"
then({Invoice_Detail.INVQTY_32} * {Invoice_Detail.PRICE_32}) *-1
else{Invoice_Detail.INVQTY_32} * {Invoice_Detail.PRICE_32})

else 0

I have 25 catagories that report for the last 12 months on this report. The above formulas are for the lastfullmonth and its previous month, but I use the same basic formula for the preceding 10 months as well by adding: ("m", -NUMBEROFMONTHS...

ideas?

Disclosure: I work in a small firm and have been forced to "work" my way through crystal; thus, I'd appreciate you putting the cookies on the bottom shelf, as it were, and help me understand your suggestions.

Many thanks in advance.

 

[dgillz]

Why aren't you simply using:

if ({Invoice_Detail.INVDTE_32} in LastFullMonth

This will pick up every single day of the prior month, whereas maximum(LastFullMonth) will give you the 28th, 29th (leap year February) or 30th depending on the month you are talking about.

If you want the month before last, write a formula to get the last day of that month, including the 31st if applicable.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[khekking]

Dgilz,

Thanks for the response. The goal is to run the report following month end and gather information for the lastfullmonth and the eleven previous months and report by category for last 12 months on a month by month basis.

I understand the lastfullmonth, but you lost me on.. "If you want the month before last, write a formula to get the last day of that month, including the 31st if applicable."

Wouldn't such a formula have to be updated each month moving forward for all 25 categories and for all 12 months? If not, do you have an example?

Thanks

 

[lbass]

I think you should use formulas like this instead of lastfullmonth:

//{@lastmonth):
if {Invoice_Detail.INVDTE_32} in dateserial(year(currentdate), month(currentdate)-1,1) to
dateserial(year(currentdate), month(currentdate),1)-1 then...

//{@twomonthsago):
if {Invoice_Detail.INVDTE_32} in dateserial(year(currentdate), month(currentdate)-2,1) to
dateserial(year(currentdate), month(currentdate)-1,1)-1 then...

//{@threemonthsago):
if {Invoice_Detail.INVDTE_32} in dateserial(year(currentdate), month(currentdate)-3,1) to
dateserial(year(currentdate), month(currentdate)-2,1)-1 then...

-LB

 

[khekking]

Thanks, lbass, I'll give that a try tomorrow. Very much appreciated.