Crystal 8.5 - Need help with formula that add/subracts

[christinetjx]

I have a report that displays total calls logged by a Hotline, grouped first by Month, then Assignment (where the calls were escalated to).

The first assignment group listed is 'Closed on Initial' (calls closed right away). The remainder assignment groups display below that. So it looks like this:

January Calls: 5000

Closed on Initial 2500
Team A 400
Team B 1000
Team C 1100

February Calls: 4479

Closed on Initial 2315
Team A 1008
Team B 156
Team C 1000


NOTE: 'Closed on Initial' calls are displayed using this formula:

If isnull ({incidentsa3.assignment}) or {incidentsa3.assignment} = '' then
'CLOSED ON INITIAL'
else
({incidentsa3.assignment})

I need help creating a formula that subtracts the total assignment calls EXCLUDING 'Closed on Initial', from 'Closed on Initial'.

So using the above example, i would total Teams A, B & C, then subract that total from Closed on Initial.

Thank you in advance for your help.

Christine

 

[lbass]

First let's call your assignment formula {@assign}. Create two formulas:

//{@Closed}:
if {@assign} = "Closed on Initial" then {table.amt}
//or 1 if you are counting something

//{@Other}:
if {@assign} <> "Closed on Initial" then {table.amt}

Then create a formula {@diff}:

sum({@Closed},{table.group}) - sum({@Other},{table.group})

-LB

 

[christinetjx]

i apologize... i'm having a problem with the @diff formula, particularly the {table.group} piece... i don't understand what should be there...

you mean something like this?

sum({@Closed},GroupName ({incidentsa3.assignment})) - sum({@Other},GroupName ({incidentsa3.assignment}))

sorry

 

[lbass]

Assuming your outer group is on date (monthly), then use:

sum({@Closed},{table.date},"monthly") - sum({@Other},{table.date},"monthly")

Substitute your date field for {table.date}.

-LB