ROW_TOTAL: can I insert a computation within it?

[kaeserea]

Hi!

I need to generate a report about telephone calls which should look like this:
[tt]
incoming_calls outgoing_calls total
-------------- -------------- -----
number av_duration number av_duration number av_duration
------ ----------- ------ ----------- ------ -----------
123 207 13 304 136 256
[/tt]
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

 

[FrankExp]

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

 

[kaeserea]

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:
[tt]
SUM ANSWERED_CALLS
COMPUTE AVETIME/D12 = SUM.TIME / SUM.ANSWERED_CALLS;
ACROSS TYPE_OF_CALL
BY COMPANY
[/tt]
Where TYPE_OF_CALL is either incoming_calls or outgoing_calls.

Eva

 

[FrankExp]

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

 

[saighton]

Hi Eva,

Have you tried inserting

ON COMPANY RECOMPUTE MULTILINES

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

 

[kaeserea]

Hello Andy,

sorry but this does not work.

Eva

 

[kaeserea]

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
answered_calls
not_answered_calls
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:
[tt]
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
... ... ... .. .. ... ...
[/tt]

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:
[tt]
-* 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
SUM ANSWERED_CALLS
TOTAL_TIME_OF_CALLS
BY COMPANY
BY KOMTEXT AS TYPE
RUN
FILE CTMMIN
SUM ANSWERED_CALLS
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
[/tt]

Created with much help from Frank!

Eva

 

[focwizard]

Let me respond by starting with your procedure:

SUM ANSWERED_CALLS
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.