Crystal Sum Function based upon a Condition

Posted on 2009-02-11
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
    LVL 10

    Expert Comment

    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 100

    Expert Comment

    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

    Woops!  Missed that. Thanks mlmcc!

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
    This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now