Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Finding Median Value

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Finding Median Value
 Login/Join
 
Member
posted
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


WebFOCUS MRE
Windows XP
Output: Excel and sometimes PDF.
 
Posts: 10 | Registered: November 14, 2007Report This Post
Virtuoso
posted Hide Post
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




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Platinum Member
posted Hide Post
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 ...


Focus Mainframe 7.6.11
Dev Studio 7.6.11 and !!!
PC Focus, Focus for OS/2, FFW Six, MSO
 
Posts: 134 | Registered: November 06, 2007Report This Post
Virtuoso
posted Hide Post
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)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
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 ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
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.


WebFOCUS MRE
Windows XP
Output: Excel and sometimes PDF.
 
Posts: 10 | Registered: November 14, 2007Report This Post
Member
posted Hide Post
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?


WebFOCUS MRE
Windows XP
Output: Excel and sometimes PDF.
 
Posts: 10 | Registered: November 14, 2007Report This Post
Virtuoso
posted Hide Post
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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....


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
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.


WebFOCUS MRE
Windows XP
Output: Excel and sometimes PDF.
 
Posts: 10 | Registered: November 14, 2007Report This Post
Guru
posted Hide Post
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


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Guru
posted Hide Post
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.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Master
posted Hide Post
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
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
Pat, you are correct in the syntax you specified.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Master
posted Hide Post
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
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
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  


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Finding Median Value

Copyright © 1996-2020 Information Builders