Subtracting one field from another field

[gennaroalpha7]

Hi-

This thread is a continuation of thread767-1721749. I tried to figure this out on my own, but I need help. Thanks in advance.

I have two fields, (tracker) 'CallLog.Tracker (string)' and (ticket closed date) 'CallLog.ClosedDate (string)'.

Then I have a (count) 'Count of CallLog.Tracker' for the tracker field. Throughout the day the (trackers)anlaysts close tickets.

What I am trying to do for my (count) is 'Count of CallLog.Tracker' to subtract the tickets that have been closed when the report is ran, which can be anytime throughout the day. So then, I'll have a count that subtracts the tickets that have been closed.


Thanks for the help.

Alpha7

 

[xxyyzz49]

You can easily count the total number of records with a summary field. Using a running total field with an evaluate formula can do a count based on the value of the tracker field or the value of the closed date field. Then create a formula to get the counts for the day.

Pete

 

[gennaroalpha7]

Hi PMSawyer -

Due to my experience level can you please be a more specific.

Thanks.

Alpha7

 

[xxyyzz49]

The report selects records from the database closed on the current date or still open.

Count ({CallLog.Tracker}

is a summary of the number of records created using the summary command.

#ClosedToday is a running total that counts the closed dates based on a evaluation formula:

date({CallLog.CloseDate} ) = currentdate

It processes all records and does not reset.

@LeftOpen is a formula:

Count ({CallLog.Tracker}) - {#ClosedToday}

That subtracts from the summary count of Tracker the running total #ClosedToday giving the number of records left open.

 

[gennaroalpha7]

Thanks, I'll try your suggestion...

After many trials and errors....

I created a 'Running Total Field' called 'ClosedDate' based on your first suggestion...

1. Summarized the Callog.CallStatus field and 'Type of summary...Count'

2. Used a formula for evaluation....to count the closed tickets for the day...'Count ({CallLog.CallStatus}) <> 0 and({CallLog.CallStatus}) = "closed"'

3. And Reset it on 'On Change of Group'

And placed that in 'Group Footer One'

Now I need to subtract this from the 'Count of CallLog.Tracker' which is a count of all tickets the tracker/analyst has.



What do you think about this approach?

Thank you.

Alpha7

 

[gennaroalpha7]

Hi -

On the @LeftOpen formula above, it's not working because the CallLog.Tracker field is grouped by Tracker/Analyst.

It's currently subtracting the 'ClosedDate' subtotal of closed tickets from the total tickets. Here is the formula for the @LeftOpen running total...'Count ({CallLog.Tracker}) - {#ClosedDate}'

It should be the 'Summary Count of CallLog.Tracker (number)' which gives a subtotal of tickets per tracker/analyst - (minus) the 'ClosedDate' running total. To give the tickets currently open per tracker/analyst.


Can the 'ClosedDate' be subtracted from a 'Summary Count of CallLog.Tracker (number)'?

Thanks.

Alpha7

 

[xxyyzz49]

A count of tickets closed today by a tracker can be subtracted from a tracker count. That would give a open ticket number for the day.

 

[gennaroalpha7]

I Did that based on your suggestions...Thank you. The (@LeftOpen) Caluculation looks like this....

Count ({CallLog.Tracker}, {CallLog.Tracker}) - {#ClosedDate}

, which gives the Open Ticket amount.

The @ClosedDate Running Total calculation looks like this...

Count ({CallLog.CallStatus}) <> 0 and({CallLog.CallStatus}) = "closed"

, which gives the Closed ticket amount.

One more calculation please....

1. There are two fields...they are (@Level1) Level 1 and (@Level2) Level 2. Both of these fields list the name of the person in the group.

2 There is another field (CallLog.Tracker) that lists ALL of the trackers...this field is grouped.

Is it possible to add the subtotals of level 1 and level 2 tickets and then add them together to make a total?

Thanks in advance for your help.

Alpha7

 

[gennaroalpha7]

Anyone there?