Problem with Manual CrossTab and DateAdd Formula

[DanVincent]

I have been beating my head in over this one,

Here is the deal.
I am still new to crosstabs and crystal. I am using crystal reports 2008.

I have a requirement to make a report that will count the number of sales{Order.SalesID} and {Transaction.InventoryCount} for a 6 month date range {TransactionDetail.Date}, compare the same sales data to last years 6 month date range and return a total.
Once totals are in, I need a percentage of increase/decrease between the two date ranges.

Lastly take the same sales data and pull 8 weeks forward of data to project sales.

I think im really close to completion but having real trouble with the Date Ranges.

I set up my manual crosstab and Limit by PartID.
then I have Params set up to prompt for 4 date ranges {@CYStartDate, @CYEndDate, @LYStartDate and @LYEndDate)

I use these for in the DateAdd formula
"{TransactionDetail.Date} < DateAdd("m", -1, {@CYStartDate})) And
{TransactionDetail.Date} > DateAdd("m", 1, {@CYEndDate})"

The problem with this is it ONLY returns a true/False boolean and I cant seem to figure out how to make it return something that can be summerized on.

I figure that I have the string wrong, but no matter how much I work on it. I cant seem to get anything different out of it.

Any ideas?

At this point if I can get meaninful counts from the date ranges I can summerize in a manual cross tab and do my percetages on those summeries.

Thanks in advance,
Dan

 

[kenhamady]

If you want to summarize records that meet a Boolean condition you use an If-Then like this:

if
{TransactionDetail.Date}< DateAdd("m", -1, {@CYStartDate})) And
{TransactionDetail.Date} > DateAdd("m", 1, {@CYEndDate})
then 1
else 0

Now your result is a number that can be summarized.

Note that your summary operation for this formula should be a SUM rather than a COUNT even though you want to know how many records qualify. That is because a zero 'counts' as a value but is ignored in a SUM.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lbass]

As an aside, I think you might have your signs reversed--right now it looks like you are trying to identify records where the date is earlier than the start date AND later than the end date.

-LB

 

[DanVincent]

Thanks so much for the responces,
That looks to have done the trick, I tried an If-than but got an error when I attempted to implement it last time.

Any chance there is a change to this solution for CR 10?

 

[kenhamady]

Well, if you did an if-THAN it wouldn't work in any version but an if-THEN works the same in all versions.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[DanVincent]

haha, lets hope not, =)
thanks again you guys are the best.

 

[DanVincent]

I went to use this formula in crystal 10 and got this error
at the
then 1
with the 1 highlighted and message
"a statement is expected here"
this formula works great in 2008 btw

 

[kenhamady]

change it from "Basic Syntax" at the top to "Crystal Syntax"

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[DanVincent]

darn it... i just went and checked that and was going to come back with nvrmind.