Skip to Content

Need help with formula to filter based on certain conditions

Mar 15, 2017 at 11:24 PM


avatar image

So I have a fairly specific question. I have a table with 6 columns:

Date, Workcenter, PartNo, UnitPrice, PcsFinished, and Output (UnitPrice x PcsFinished)

If Pcsfinished is equal to 1, I sum the Output to get the "Total Output"

If PcsFinished is equal to 0, I sum the UnitPrice and call it "In Process"

Problem is, in the same day, pieces can go from "In Process" to a part of "Total Output" in the same day. A part may be in process in the morning and have line such as

3/15/17 - Cutting - 10320.1 - $2,550 - 0 - $0

and later in the day it gets completed so it creates another line item in our database that looks like:

3/15/17 - Cutting - 10320.1 - $2,550 - 1 - $2,550

Problem for me is that in my report, it shows total output as $2,550 and total in process of $2,550 as well, which is wrong. Once the part is complete (1), it should no longer show as part of "In Process". I tried this so far in my formula for "In Process" and it didn't work:

{TimeTicketDet.WorkCntr} = 'Cutting' and

{TimeTicketDet.PiecesFinished} = 0 and

Maximum({TimeTicketDet.PiecesFinished}) <> 1

Basically I want to sum if PcsFinished = 0, unless the max PcsFinished for that PartNo is equal to 1. I guess the part I'm missing is somehow associating the PartNo to my formula, no idea how to go about this. Any help is appreciated, thanks

10 |10000 characters needed characters left characters exceeded

Using Crystal 2016 btw

* Please Login or Register to Answer, Follow or Comment.

1 Answer

Ted Wagner Mar 16, 2017 at 05:34 PM


10 |10000 characters needed characters left characters exceeded