Generate Ratio based on group totals 1

[MTarkington]

Good morning,

I am trying to generate a ratio between the totals of a group.

I am grouping on field AUTHORIZATION.
I have a total that counts all of the AUTHORIZATIONS.
(the data can be Approved, Pending, Denied)
I want to generate a ratio of denied to approved.

example:
Totals are: Approved = 10, Pending = 1, Denied = 5
The ratio of Denied to Approved is 1:2.

I am not sure how to do the calculation when the group counter is set back to zero every time the group changes.

If I need to supply more information, please let me know.

TIA,
Mark

 

[IdoMillet]

Just create a formula that divides one group total by another.

If you are using a Running Total to compute this, you must place the formula in a Group Footer. If this is an issue for you, there is an easy way around it.

hth,
- Ido



Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MTarkington]

Thanks for the reply.

I've been trying to create a running total field, but either I get no data or after it zeroes out after a new group, I can't pull the data for a calculation.

I'm sure this is alot simpler than I am seeing it, but I just can't figure it out...

Thanks again,
Mark

 

[Madawc]

You need three running totals, each of which accumulates for one type, Approved or Pending or Denied. These should accumulate once per group and never be reset.

At the end of the report, use these totals to get your ratio. This can be done in a formula field.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Or instead of running totals, you can create formulas for the detail section like:

//{@Approved}:
if {table.authorization} = "Approved" then 1

//{@Denied}:
if {table.authorization} = "Denied" then 1

Then create a third formula for the report footer:

sum({@Denied})/sum({@Approved})

-LB

 

[MTarkington]

Thanks for the input, all is appreciated.

I do have a rather simple question:
Why doesn't this formula increment on every record? It is just a counter, but it doesn't count every instance of the field in which I am looking.

whileprintingrecords;
numbervar numDenied;
if trim({tablename.authorization}) = "Denied" then
numDenied = numDenied + 1;

numDenied;

I know there has to be something wrong with it, but I guess the deadline is starting to work on me, because I can't get past this counter issue..

Thanks,
Mark

 

[lbass]

There is nothing inherently wrong with your formula, although there is no reason to use a variable here. I'm assuming this formula is in the detail section. You should have a separate display formula though for the report footer, if you still want to use a variable.

It might be that "Denied" is entered in multiple ways, perhaps mispelled or using uppercase.

-LB

 

[MTarkington]

Well, right now, that formula gives me a 0.00 as a value.

If I don't use a variable, how will the formula increment?

When I tried what you suggested above, it never incremented, it stayed at 1.00.

The value can't be misspelled, it is coming from a data dictionary in the software. There are only 3 possible selections. (Pending, Approved, Denied)

Any suggestions?

 

[lbass]

If you place my formula:

if {table.authorization} = "Denied" then 1

...in the detail section, next to {table.authorization}, does it show the correct result?

To subtotal formulas like this, all you need to do is right click on the formula and insert a summary (sum, not count).

Also, where are you placing your variable formula? Did you also create a reset formula? If so, where did you place this?

-LB

 

[MTarkington]

Thank you so much.

I didn't quite understand what you were saying, but now, after I realized that it was't supposed to be accumulating, I'm getting the results I need.

Thanks again,
Mark