Crystal Reports formula issue

I am trying to create a summary that only looks at the past months data.  The query pulls the past 6 months, but I only have a summary for the past month.   I am using a case statement as below...

select (Month({Ens_CustDashReport;1.order_date}))
      case  Month(CurrentDate - 1):
    ((Sum({Ens_CustDashReport;1.On Time shipment})/count({Ens_CustDashReport;1.co_line})) * 100)

   Default : 20

This doesn't work it always looks at the entire collection.  Can anyone help?
stevendeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Try a formula like
Name - LastMonthShipped
If ({Ens_CustDashReport;1.order_date} In LastFullMonth then
     {Ens_CustDashReport;1.On Time shipment}
Else
    0

Open in new window


Add another formula
Name - LastMonthCount
If ({Ens_CustDashReport;1.order_date} In LastFullMonth then
    1
Else
    0

Open in new window


Summarize with
Sum(LastMonthShipped) / Sum(LastMonthCount) * 100

Open in new window


mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
The problem is that CR summary funtions (Sum, Count, etc.) always give you the total for the entire report (or group).  It's not conditional.  For example, if you have a formula like this:

if {field1} = "Y" then
  Sum ({field2})

 Whenever field1 is "Y", you'd get the total for field2 for the entire report.  If field1 is not "Y", you'd get 0.

 You can use formulas like the ones that mlmcc posted to control which records are included in a summary, although the last formula should be:

Sum({@LastMonthShipped}) / Sum({@LastMonthCount}) * 100


 FWIW, I'd probably use formulas like those, but, if you don't need the summaries until the end of the report, running totals would be another option.  You would set the summary and count to be evaluated when the date was in the desired month.

 James
0
stevendeveloperAuthor Commented:
I worked! Thank you very much for the help, and for helping me to learn a bit more.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.