×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# ROW_TOTAL: can I insert a computation within it?

## ROW_TOTAL: can I insert a computation within it?

(OP)
Hi!

I need to generate a report about telephone calls which should look like this:

incoming_calls       outgoing_calls       total
--------------       --------------       -----
number  av_duration  number  av_duration  number av_duration
------  -----------  ------  -----------  ------ -----------
123       207         13       304         136      256

I can create the total for number by ROW-TOTAL. Yet if I use it, of course the average call duration is summed up: 207+304=511. Which is not correct. I need to compute this field by summing up the length of the calls divided by the total sum of calls.

So my question is whether it is possible the make a calculation within the ROW-TOTAL statment and if not how I can solve this problem in another way.

Thanx for help.
Eva

### RE: ROW_TOTAL: can I insert a computation within it?

Eva,

Use a define:

DEFINE FILE filename
TOTALCALLS/I9 = INNUMBER + OUTNUMBER;
AVGTIME/I9  = ((AVGINTIME  * INNUMBER) +
(AVGOUTTIME * OUTNUMBER) / TOTALCALLS);
END

Frank van de Kant
http://www.explicit.nl

### RE: ROW_TOTAL: can I insert a computation within it?

(OP)
Hello Frank,

I thought about that as well, but the problem is that I generate the first line (incoming_calls, outgoing_calls) with an ACROSS statement and then the DEFINE suggesstion does not work. Here is my procedure:

COMPUTE AVETIME/D12 = SUM.TIME / SUM.ANSWERED_CALLS;
ACROSS TYPE_OF_CALL
BY COMPANY

Where TYPE_OF_CALL is either incoming_calls or outgoing_calls.

Eva

### RE: ROW_TOTAL: can I insert a computation within it?

If the number of Typecalls is 2 and never more you can define these as well.
Then you can build 2 different requests. The first builds detailled info, the second builds the total info. Both can produce averages and absolute values. In the end you can build one file by using a MATCH-statement.
Then you can match the records on the companynumber and weeknumber.

If you really need help I can code it for you, but then I need a little bit more time.

Frank van de Kant
http://www.explicit.nl

### RE: ROW_TOTAL: can I insert a computation within it?

Hi Eva,

Have you tried inserting

ON COMPANY RECOMPUTE MULTILINES

after the BY COMPANY line? This usually works for me.
Andy

### RE: ROW_TOTAL: can I insert a computation within it?

(OP)
Hello Andy,

sorry but this does not work.

Eva

### RE: ROW_TOTAL: can I insert a computation within it?

(OP)
Hi!

For everybody who is interested in the solution, here it is:

These are the columns of the DB2 source:
company
call_type
day_of_week
total_time_of_calls

Where call_type is either e for coming from external, or i for coming from within the company.

The report should look like this:

company  calls from outside  calls from within  all calls
-------  ------------------  -----------------  -----------
a        av_time    a        av_time   a  av_time
---       --------  ---       -------  --- -------
xyz      1234          146   45          79     1279    128
...       ...          ...   ..          ..      ...    ...

Where a means answered calls and av_time means the average time one call took.

The problem was to make the "all call" column which you cannot create with ROW-TOTAL, because that would means that two averyge times are added. And this is not an average time one call took regarding all calls! You need to make a compute here which is not possible within a ROW-TOTAL.

So Frank helped me out by suggesting a mixture of DEFINE and MATCH. And this works:

-* 1.) create all call types:

DEFINE FILE CTMMIN
KOMTEXT/A15 = DECODE CALL_TYPE('e' 'calls from outside'
'i' 'calls from within');
GESKOMTEXT/A15 = 'all calls';
END

-* 2.) create a table which includes all call types

MATCH FILE CTMMIN
TOTAL_TIME_OF_CALLS
BY COMPANY
BY KOMTEXT AS TYPE
RUN
FILE CTMMIN
TOTAL_TIME_OF_CALLS
BY COMPANY
BY GESKOMTEXT AS TYPE
AFTER MATCH HOLD AS TYPES FORMAT FOCUS OLD-OR-NEW
END

-* 3.) create the actual report

SET NODATA = '-'

-* use TABLE FILE TYPES
-*     PRINT *
-*     END
-* to see why I need a DEFINE to clue some columns created by the HOLD together

DEFINE FILE TYPES
ANSW/I11 = E03 + E05;
TIME/I11 = E04 + E06;
END

TABLE FILE TYPES

SUM ANSW AS 'a'
COMPUTE AVTIME/D12 = SUM.TIME / SUM.ANSW; AS 'av_time'
BY COMPANY
ACROSS TYPE AS ''

END

Created with much help from Frank!

Eva

### RE: ROW_TOTAL: can I insert a computation within it?

Let me respond by starting with your procedure:

COMPUTE AVETIME/D12 = SUM.TIME / SUM.ANSWERED_CALLS;
ACROSS TYPE_OF_CALL
BY COMPANY

This will create THREE columns for each ACROSS value. The first is ANSWERED_CALLS. The second is SUM.TIME (NOPRINTed, since you use it in a COMPUTE but don't reference it in the request). The third is AVETIME. The columns can be refererred to by their position, as 'Cn'.

After the ACROSS (but BEFORE the BY) add the following:

COMPUTE
ANSWERED_CALLS/I9 = C1 + C4 + C7 + C10 + C13 + C16;
COMPUTE AVETIME/D12 = (C2 + C5 + C8 + C11 + C14 + C17)/ANSWERED_CALLS;

This uses COLUMN ('C') notation, to indicate which columns are used in the calculations.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!