Trouble counting a formula field

[demunchkin]

Our lab director came to me with a problem on a report she was trying to customize. We get pre-made Crystal reports for our lab system from an outside company that provides the lab system. Many to most of the field names are based through views.

Here is the problem: there is a field that calculates turnaround time for a lab specimen. The result is a whole number ranging from -1 to maybe 60 (minutes). The goal is to count how many have a 1 minute turnaround time, 10 minute turnaround time, etc. When a count function is placed for that field, the results are severely incorrect. Where I can see four with 1 (minute), the total will say 212. Where there may be ten with 5 (minutes), the total says 428. Every single total is very wrong. We can't figure out what exactly is being counted. Does anyone have any ideas on what could be causing this or any ideas on how to get this to work correctly? Any help would be extremely appreciated.

Devin

 

[rosemaryl]

Dear Demunchkin,

In this situation what you want to do is to create formula fields that test for the value and then sum those.

For example you said 1 minute turnaround so you would create one formula for each "turnaround"

If {@turnaroundtime} <= 1 then 1 else 0

Place on your report in the details, right click and sum by the group or for all groups and you will get the number that met the criteria.

Do this for each time increment.

If you need more help, let me know.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[SBendBuckeye]

Check your groupings and if the count is not being done at the detail level you may want to look at Distinct Count. As an example, say you were doing a count on last names in a family table where the report printed a line for every person in the family like below:

Last Name Smith - Grouping on Last Name

First Name 1 Gary
First Name 2 Leslie
First Name 3 Andy
etc,

If the count was at the Last Name, you would get 3, 6, 2, etc depending on the number of family members for each last name. If you changed to Distinct Count you would get 1 for the above, etc.

If this doesn't work, post some examples of where the count is being done and we can look at it again.

Good Luck!

 

[synapsevampire]

I think what you want is to Group by the turnaround time field, then place the turnaround time field in the details section, right click it, and select insert->summary->count, which will place it in the group footer. Now placing both the TT field and this new count in the group footer, and then suppressing the details and group header section will result in something like.

TurnAround Time (minutes) Count
1 12
5 3
10 22

etc.

Is that what you seek?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

I think the problem might be in the placement of your counting formula. It sounds to me as though it may be in detail section whereas it probably should be in a group based on &quot;lab Specimin&quot; Jim Broadbent

 

[Ngolem]

I mean it should be in the Group Header....it has been a while Jim Broadbent

 

[rosemaryl]

Jim,

I noticed I hadn't seen any posts from you in the last month or so - I hope all is well with you.

Demunchkin,

We are all giving you ideas here with no feedback - can you give us a heads-up? Is your problem solved?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[demunchkin]

I was unable to make it to lab yesterday (other unforseen problems). I will work on it today and give you some feedback. Thanks in advance for all the ideas.

 

[demunchkin]

The solution turned out to be very simple. In re-laying out the report, I realized the company that built the report had a great deal of data suppressed, each field with a suppress duplicates. In using the count function, I was getting a count of all records plus duplicates. I placed a distinct count summary in the group footer for a key field, and the count came out correctly.

Thank you for all your help. It was working with your suggestions that brought me to the realization that it was counting duplicates.

Devin