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

Solved

Posted on 2009-02-11

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.

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.

4 Comments

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

Evaluate using a formula

CurrentDate < {mydatabase.expdate}

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

mlmcc

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Enhanced For loop vs ForEach Loop | 4 | 42 | |

triangle challenge | 4 | 36 | |

Crystal Report Image from SQL field with file location | 3 | 29 | |

Specific format | 21 | 105 |

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

Connect with top rated Experts

**9** Experts available now in Live!