Don't count values unless full month 2

[Crystalguru]

I have a manual crosstab that has values for each month and each year. The final numbers are averages for each month. What I would like to do is, if a month is not yet over (for this example March) then don't report the numbers.
My report:
YR Jan feb mar
2002 5 6 3
2003 3 5 1
Avg: 4 11 3

Since March of 2003 year has not completed the data is not complete. So, I don't want ANY values to appear. Is there a formula to do this? Such as
If date_day in 03-01-2003 to end of month
then #value
else
0


thanks

 

[pelajhia]

create a formula:
maximum(lastfullmonth)
to give you the last day of the last full month.
In the select expert, do a select where
{yourdatefield} < {dateformula}

 

[synapsevampire]

Or in the record selection just use:

{table.field} <= lastfullmonth

This will generate the proper SQL to limit the rows returned to last month, and pass it to the database so the performance will be optimized.

-k

 

[Crystalguru]

Won't this limit my results to just the last full month. When I need last year 2002, and 2003?
YR Jan feb mar
2002 5 6 3
2003 3 5 1
Avg: 4 11 3

I only want March to be zero since it hasn't been a full month. Or is this what the lastfullmonth function does?

 

[pelajhia]

No, both ideas should only keep you from showing data from March. When March is over, or 'full', it will show, too. This will not affect the beginning date of your date range, if you have put one in the select, for instance.

Oh, and definitely use a <= rather than just a < whichever formula you use, as otherwise you might find items with datetimes in the middle of the last day being skipped.

 

[Crystalguru]

Cool, it worked. Surprised me. Thought it would restrict the data. But understand that the other months are FULL months. The crystal help file didn't explain it all that well. Thanks to everyone!