My customer just informed me that they need the median average handle time in seconds instead of the average. I am totally stumped. How would I go about modifying what I have below to produce the median value for Avg_Handle_Seconds ?

Thanks in advance for your help.

DEFINE FILE SQLOUT ADD
FunHours/D12.1=Total_Handle_Seconds / 60 / 60;
WorkHours/I5C=FunHours * 60 * 60;
END
TABLE FILE SQLOUT
SUM
CNT.DST.EmpID/I5C AS '# Reps'
AVE.Avg_Handle_Seconds/I11C AS 'median time in seconds'
AVE.Total_Handle_Seconds/I11C AS 'handle time in seconds'
Quanity/I11C
FunHours/D8.1 AS 'Func. Work in hours'
WorkHours/I11C AS 'Work in Seconds'
BY System
BY FunctionName
ON System UNDER-LINE

Mullman

do you know yourself what the median value is?
not the formula but the description?

In my opinion it is the middle value when you rank all the values in a sequence low to high.
So if you do that and count the number of values, divide that number by two you know which one you need.

so suppose you want to rank the average sales by country you get

step one
table file car
sum sales
by country
on table hold
end
step two
table file hold
print country
ranked by sales
end

now you see the sales sorted with a rank number (1-5)
the median should be number 3 (1+5)/2

that's it

Hi guys,
ANALYSE can do that (This out-of-time part of Focus was ther to fight with SAS ...)
Re-Read all that you (should) know about Quartiles, Deciles, etc ... + putting aside the meaningless Values : too high or too low.
But the idea is to Sort the values to which you add a Rank Value (Re-Read LIST which is also an out-of-time Focus VERB : Prefer Define With 1 + LAST or Compute 1 + LAST)
Focusly
PS : What I like is 1st Decile, 9th Decile, and Quartiles -Median being 2nd Quartile-). But very few people buy me all those ...

Baillecl

You are right. But to send a new focus user something from the old days that is not in the GUI would not have been very helpfull.
I tried to avoid that by symple explain how he can do it. (I would use ANALYZE if it would work)

I think if you type 'median' in the advanced search, you should get a number of hits. I remember reading articles about median calculation using FOCUS written by Noreen Redden.

Found this piece of code using the advanced search for finding the median (middle) value:

SET HOLDLIST=PRINTONLY
TABLE FILE EMPDATA
COUNT PIN NOPRINT
SUM PIN NOPRINT
COMPUTE MED/I3=CNT.PIN/2;
ON TABLE SAVE FORMAT ALPHA AS SAVEME
END
-RUN
-READ SAVEME &MED.3.
DEFINE FILE EMPDATA
CNT/D15=CNT +1;
END
TABLE FILE EMPDATA
PRINT PIN
IF CNT EQ &MED
END 

Hope this helps ...

Getting close but still stumped...

Thanks for all the help so far. I'm getting close but it still isn't working properly. Here's what I came up with:

TABLE FILE SQLOUT
COUNT Avg_Handle_Seconds NOPRINT
SUM Avg_Handle_Seconds NOPRINT
COMPUTE MED/I3=CNT.Avg_Handle_Seconds/2;
ON TABLE SAVE FORMAT ALPHA AS SAVEME
END
-RUN
-READ SAVEME &MED.3.
DEFINE FILE SQLOUT
CNT/D15=CNT +1;
END
TABLE FILE SQLOUT
PRINT Avg_Handle_Seconds IF CNT EQ &MED
ON TABLE SAVE FORMAT ALPHA AS SAVEME2
END
-RUN
-READ SAVEME2 &NEWMED.3.
DEFINE FILE SQLOUT ADD
FunHours/D12.1=Total_Handle_Seconds / 60 / 60;
WorkHours/I5C=FunHours * 60 * 60;
MedHandle/I4=&NEWMED;
END
TABLE FILE SQLOUT
SUM
CNT.DST.EmpID/I5C AS '# Reps'
MIN.Avg_Handle_Seconds/I11C AS 'min handle time'
MedHandle AS 'med handle time'
MAX.Avg_Handle_Seconds/I11C AS 'max handle time'
AVE.Total_Handle_Seconds/I11C AS 'avg total handle time in seconds'
Quanity/I11C
FunHours/D8.1 AS 'Func. Work in hours'
WorkHours/I11C AS 'Work in Seconds'
BY System
BY FunctionName
ON System UNDER-LINE


If I stop at "PRINT Avg_Handle_Seconds IF CNT EQ &MED" it prints the right median number which is definitely a step in the right direction. But when I run the whole report that med handle time column is only filled with zeros. What did I miss?

Thanks again for all your help. This has been a real eye-opener.

I'm still stumped on this issue and I'm wondering if I'm properly handling the variable assignment for the value I'm trying to print on the report. Please let me know if this is correct. The line -READ SAVEME2 &NEWMED.3. stores the value I'm trying to get into the variable &NEWMED, right?

almost

-READ SAVEME2 &NEWMED.3.

should be

-READ SAVEME2 &NEWMED.I3.
or
-READ SAVEME2 &NEWMED.A3.
or
-READ SAVEME2 &NEWMED

what is your error

do a TYPE &NEWMED to see the result
put -SET &ECHO=ALL; in your code for debugging.

did you ad the line -RUN after the code that generates the hold file.
Try adding ? HOLD after the RUN command to see the real format of the hold file.

Hmmm, I was always under the impression that the SQLOUT dataset was a very very temporary dataset, available only immediately after the sql request.
Must have missed something.
But, just to humor me, what would happen if the first thing you do after the sql request is to hold all data in a hold file and then use that hold file in the subsequent requests?
Does this make any difference?
And, do you have SET HOLDLIST=PRINTONLY active? If not, the SAVEME record contains more then just the median number, it also contains both NOPRINT fields....

Frank,
No error. That column just shows up with zero instead of the expected median value which, based on my calculation, should be 769. That's the number I get when I comment out the line ON TABLE SAVE FORMAT ALPHA AS SAVEME2... This leads me to believe that I'm doing something wrong with the variable assignment.

My understanding of the median is the middle value when the list contains an odd number of items and the average of the two middle values when the list contains an even number of values.

Here is an example that I think is working, based on that. No need to worry about populating and amper variable.

Comment out the NOPRINTs and the WHERE to see what it is doing.

TABLE FILE CAR
COUNT RCOST NOPRINT
BY COUNTRY
PRINT RCOST NOPRINT
  COMPUTE COUNTER/I8   = IF COUNTRY EQ LAST COUNTRY
                         THEN COUNTER +1
                         ELSE 1; NOPRINT
  COMPUTE MEDCNT/I8    = (C1+2)/2; NOPRINT
  COMPUTE MED/D7       = (RCOST + LAST RCOST)/2;
 
BY COUNTRY
BY RCOST NOPRINT
WHERE TOTAL COUNTER EQ MEDCNT;
END

Also, there are some limitations on the usage of SQLOUT. I've been trying to find the reference but the only thing I have found so far is one for MF FOCUS DB2. It specifically refers to DEFINE not being supported with SQLOUT, and I believe that is the case in all rdbms passthru requests.

I'm with Piipster, I don't think you can do a define on a SQLOUT. In order to give your SQLOUT a "name", all you have to do is put:
TABLE
ON TABLE HOLD AS MYNAME
END
(I hope on right on this) before the ; at the end of your passthru. Then you can use the file the same as anyother focus flat file. And you don't lose it if you do a second passthru.

Pat, you are correct in the syntax you specified.

Thanks Ginny, it has been so long since I have done it that I couldn't remember if the code went before or after the semicolon.

Pat, I just reread your post and realized I'd misread an important point. The code goes AFTER the semicolon.

SQL rdbms_engine
SELECT ....
FROM ....
WHERE ....
;
TABLE 
ON TABLE HOLD AS MYNAME FORMAT ALPHA
END