Crystal Formula to "conditionally count/sum"


I created a series of reports that include "conditional count/sum" formulas like this:

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then 1 else 0

Unfortunately, I recently found out that some of the records are repeating because of another table that's tied to the History table.  (CONTACT -> HISTORY -> HISTORYTYPE) (My counts are incorrect if multiple contacts are included).

I'm trying to avoid rebuilding all of these reports by adding subreports for the contact information.

Is there a way to incorporate the HISTORY.HISTORYID field (string) in the formula to only sum if the HISTORYID has changed?

Also, my "totals" have to appear in the Report Header - so I don't think a "running total" will work.

I appreciate any ideas!  Thanks in advance
Who is Participating?
mlmccConnect With a Mentor Commented:
You are correct that a running total won't work.

Try this

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then 

Open in new window

Do a distinct count on that field.

SStrozAuthor Commented:
Shoot - I closed this too soon.

I should have mentioned that I have 3 formulas like this:

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then {HISTORY.HISTORYID}
if {TBL_HISTORYTYPE.NAME} = "Prospect Visit Completed" then {HISTORY.HISTORYID}
if {TBL_HISTORYTYPE.NAME} = "Trial Visit Completed" then {HISTORY.HISTORYID}

It doesn't look like it's counting correctly.  

It looks like Crystal is counting a "1" where there should be a "0".  ??

Any ideas?
I was afraid that might happen.

It is counting the ones that don't match as 1 since the return is not null

Try it this way

Create a formula
Name - NULL
Formula is nothing just save an empty formula

Change the formula above to

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then 

Open in new window

That will result in NULL for the non-matching and DistinctCount will ignore the NULLs

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.