We help IT Professionals succeed at work.

# Crystal Sum Function based upon a Condition

on
Medium Priority
454 Views
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.
Comment
Watch Question

## View Solution Only

Commented:
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
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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 <

mlmcc
Commented:
Woops!  Missed that. Thanks mlmcc!

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
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.
Access more of Experts Exchange with a free account
##### Thanks for using Experts Exchange.

Limited access with a free account allows you to:

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile