Subtracting group totals

[harrietohs]

Using Crystal 8.5

I have a report that groups on a "status" field, and I show a total count for each status.

Example:

Status #
------ -------

Active 10
Held 3
Terminated 2

Instead of a grand total that adds the above numbers, I need to subtract the Held and Terminated from the Active, so the total on above the example would be 5. Any tips on how to most easily achieve this?

Harriet
Ottawa Humane Society

 

[Cretin]

I would create a simple formula something like this.

nonact = (held + terminated)

Nonact is a field you create to hold the sum of the held and terminated.

Then I would do this in the same formula

total = (active - nonact)

where total is a field you create to hold the active - non active.

Cretin

 

[harrietohs]

Sorry, I still don't understand how I can capture the separate sums for each group so that I can use them in a formula. The sum for each status group is generated by choosing "Insert Summary" for each Status Change, with a Distinct Count of the person's ID, so each status count has the same name, i.e. DiscountCount of ID.

Harriet

 

[kskid]

But your group name differs

In the report header add the following

//@inittotal
shared numbervar total := 0;

In the matching group footer that matches your group header for status, add the following formula

//@accumTotal
shared numbervar total;
if groupname = "Active" then
total := total + (distinct count of ID,status)
else
total := total - (distinct count of ID,status)

In your report footer, simply display

//@Showtotal
shared numbervar total;
total

-lw

 

[synapsevampire]

You might use 2 Running Totals and in the Evaluate->Use a Formula place:

//Active
{table.status} = "Active"

//Held-Terminated
{table.status} in ["Held", "Terminated"]

A formula in the report footer for display could use:

{#Active} - {#Held-Terminated}

-k

 

[harrietohs]

When I try to use the following formula, I get an error message "The ) is missing"

shared numbervar total;
if groupname = "Active" then
total := total + (distinctcount of ID,status)
else
total := total - (distinctcount of ID,status)

 

[synapsevampire]

Try:

shared numbervar total;
if groupname = "Active" then
total := total + ({distinctcount of ID},{status})
else
total := total - ({distinctcount of ID},{status})

If you double click the fields in the formula editor it will automatically include the braces around the fields.

-k

 

[synapsevampire]

And remember you don't have to use this group field for display, just for grouping.

-k

 

[kskid]

For groupname and (distinctcount of id,status), need to substitute the actual group name and distinct count summaries that is listed in your report.

 

[harrietohs]

All of the above is way beyond my usual level of formula creation, but I am going to give it all a try.

Thanks

Harriet

 

[harrietohs]

shared numbervar total;
if groupname = "Active" then
total := total + (DistinctCount ({PAW Amendments.Constituent ID}, {PAW Amendments.Gift Status}))
else
total := total - (DistinctCount ({PAW Amendments.Constituent ID}, {PAW Amendments.Gift Status}))

This results in an error message: Not enough arguments have been given to this function.

Any idea what I missed?

Harriet

 

[harrietohs]

ooops..found my error and corrected formula to:

shared numbervar total;
if GroupName ({PAW Amendments.Gift Status}) = "Active" then
total := total + (DistinctCount ({PAW Amendments.Constituent ID}, {PAW Amendments.Gift Status}))
else
total := total - (DistinctCount ({PAW Amendments.Constituent ID}, {PAW Amendments.Gift Status}))

I will keep on trucking!

Harriet

 

[kskid]

Where is the formula located? It should be in the same group section where {status} is grouped.

Also, you need to substitute "groupname" with the actual groupname from your formula editor

-lw

 

[harrietohs]

IT WORKS! Thank you so much for helping me through this.

Gratefully,

Harriet