Crystal Sum Function based upon a Condition

Posted on 2009-02-11
Medium Priority
Last Modified: 2012-05-06
I have a crystal report sub report.  In this sub report I list items in inventory based upon their expiration date.  I have tried all ways to create a formula to calculate the following example:

Lot #            Exp. Date          Qty
123              01/28/09            10
124              03/01/09            12
125             12/28/09             10

So what my formula I want to accomplish is this, if the expiration date is greater than the current date then I want to have you add these values.  So for this example I would add lot 124 and 125 and come up with a total of 22.

I have went through about every possible formula I could think of and I haven't been able to quite accomplish the value of 22 I am trying to acheive.  I am really wanting to say if the date has not exceeded add those lines together.  If it has exceeded the current date then ignore the number in your calculation.

If anyone has an idea on how to accomplish this with a formula I would greatly appreciate it.
Thanks in advance.
Question by:pilotgl
  • 2
LVL 10

Expert Comment

ID: 23616925
This is fairly easy.

Create a formula as follows:

If CurrentDate > {mydatabase.expdate} then {mydatabase.qty} else 0

Then SUM on this formula.

You can suppress the formula in the details section if you don't want it to show
LVL 101

Expert Comment

ID: 23617234
Another way would be to use a running total.

Evaluate using a formula

CurrentDate < {mydatabase.expdate}

Doug's formula is almost correct.  Change the > to <

LVL 10

Accepted Solution

dougvarga earned 2000 total points
ID: 23621549
Woops!  Missed that. Thanks mlmcc!

Author Closing Comment

ID: 31545817
I appreciate the help on this.  Thanks for the heads up on the wrong sign.  I caught that and did make the change when I created the formula.  I am getting my feet beyond wet and starting to get more heavily into Cyrstal Reports.  But there is always room to learn and am still learning that it is easier to do multiple formulas versus trying to do all the functions in one formula.  Thanks again for the help and it was greatly appreciated.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Starting up a Project

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question