RE: Réf. : RE: Calculating Median
Deskin, Bob
Bob.Deskin@Cognos.COM
Sun, 1 Aug 2004 17:22:11 -0400
You're right, I forgot the NORESET on the first subtotal. Without it, you just get a value of 1 all the time.
However, you still need to have a separate second pass to get the total number so that it's accessible by the third pass. And I think that second pass probably needs a NORESET as well. It's too bad there wasn't a way to access just the last record of the subfile from the first pass or read the file and only output one record with the results of the calculations on that last pass.
Bob
-----Original Message-----
From: nicolas.au@bnpparibas.com [mailto:nicolas.au@bnpparibas.com]
Sent: July 30, 2004 4:25 AM
To: Deskin, Bob
Subject: Réf. : RE: Calculating Median
Hi Bob,
We do not require the original pass 2 if we use the statement
report summary x x-count subtotal NORESET
Instead of
report summary x x-count subtotal
in the original pass 1.
In fact using the statement
report summary x x-count subtotal
will not work as x-count will always be 1 if all the data in x are
different.
This is because in the original final pass the select statement
select if x-count = x-med1 or x-count = x-med2
will not work as x-xount (always equals to 1) will never equal to x-med1 or
x-med2
If the data in x are not always different then x-count will be in a
sequence of 1,2,3... in a cluster of value of x and the sequence will be
broken as soon as the data of x differs from the previous value. So
x-count will never be in an unbroken sequential numbers.
Do you agree ?
My 3 passes solution :
; PASS 1
access file
sort on x
define x-count integer = 1 ; increase the size if there is a lot of
records in the file
report summary x x-count subtotal NORESET
set subfile name xsub
go ; this gives the values in sequence with a sequence number
; PASS 2
access *xsub
define x-med1 integer = (n + 1)/2 if 1 = mod(n,2) else n/2 ; increase
the size if necessary
define x-med2 integer = (n + 1)/2 if 1 = mod(n,2) else (n/2) + 1 ;
increase the size if necessary
define x-1 = 1
sorted on x-1
report summary x-med1 x-med2
set subfile name xmedian at x-1
go ; this gives the record numbers of the one or two records you
want
. PASS 3
set nosubfile
access *xsub link to record 0 of *xmedian ; record 1 on OpenVMS and
OS/400
select if x-count = x-med1 or x-count = x-med2
; if x-med1 = x-med2 only one record will be selected
; if x-med1 <> x-med2 two records will be selected
DEFINE X-1 = 1
SORTED ON X-1
FOOTING AT X-1 x average x-med1 x-med2
go ; this gives the median
Look forwards to hearing your comment.
Nicholas.
Internet
Bob.Deskin@Cognos.COM@lists.sowder.com - 29/07/2004 13:25
Envoyé par : powerh-l-admin@lists.sowder.com
Pour : powerh-l
cc :
Objet : RE: Calculating Median
And then there's the QUIZ solution. Let's say you want themedian for
itemx. If the number of records is odd you want the value of x for record
(n+1)/2. If the number of records is even, you want the average (sum
divided by count) of the values of x for the two records n/2 and (n/2)+1.
I can do this report in 4 passes (remember Name that Tune). Anyone for 3?
Disclaimer: I have not tested this other than in my head, and we all know
what can happen when we only test in our heads :-)
access file
sort on x
define x-count integer = 1
report summary x x-count subtotal
set subfile name xsub
go ; this gives the values in sequence with a sequence number
access *xsub
define n = 1
define x-1 = 1
sorted on x-1
report summary n subtotal
set subfile name xtotal at x-1 ; this is the same as AT FINAL which QUIZ
doesn't have
go ; this gives the total count n
access xtotal
define x-med1 integer = (n + 1)/2 if 1 = mod(n,2) else n/2
define x-med2 integer = (n + 1)/2 if 1 = mod(n,2) else (n/2) + 1
define x-1 = 1
sorted on x-1
report summary x-med1 x-med2
set subfile name xmedian at x-1
go ; this gives the record numbers of the one or two records you want
set nosubfile
access *xsub link to record 0 of xmedian ; record 1 on OpenVMS and
OS/400
select if x-count = x-med1 or x-count = x-med2
; if x-med1 = x-med2 only one record will be selected
; if x-med1 <> x-med2 two records will be selected
report x average x-med1 x-med2
go ; this gives the median
Bob Deskin
Senior Product Manager, Application Development Tools
Cognos Inc. 3755 Riverside Drive, Ottawa ON K1G 4K9 CANADA
bob.deskin@cognos.com (613) 738-1338 ext 7268
-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com]On Behalf Of Robert Edis
Sent: July 28, 2004 7:11 PM
To: PowerHouse List
Subject: RE: Calculating Median
No, no, no ... I'm innocent I tell you!
Ordinarily I would agree but in this particular case I suggest Quick
because of simplicity. Exception-to-the-rule and all that.
Of couse I could of suggested using a 3GL tool - an external function if
you like. :)
Blue
David Morrison - Corporate <dmorrison@mcbrideelectric.com> wrote:
@font-face { font-family: Tahoma; } @page Section1 {size: 8.5in 11.0in;
margin: 1.0in 1.25in 1.0in 1.25in; mso-header-margin: .5in;
mso-footer-margin: .5in; mso-paper-source: 0; } P.MsoNormal { FONT-SIZE:
12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman";
mso-style-parent: ""; mso-pagination: widow-orphan;
mso-fareast-font-family: "Times New Roman" } LI.MsoNormal { FONT-SIZE:
12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman";
mso-style-parent: ""; mso-pagination: widow-orphan;
mso-fareast-font-family: "Times New Roman" } DIV.MsoNormal { FONT-SIZE:
12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman";
mso-style-parent: ""; mso-pagination: widow-orphan;
mso-fareast-font-family: "Times New Roman" } P.MsoAutoSig { FONT-SIZE:
12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"; mso-pagination:
widow-orphan; mso-fareast-font-family: "Times New Roman" } LI.MsoAutoSig {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman";
mso-pagination: widow-orphan; mso-fareast-font-family: "Times New Roman" }
DIV.MsoAutoSig { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times
New Roman"; mso-pagination: widow-orphan; mso-fareast-font-family: "Times
New Roman" } SPAN.EmailStyle15 { COLOR: navy; mso-style-type:
personal-reply; mso-ansi-font-size: 10.0pt; mso-ascii-font-family: Arial;
mso-hansi-font-family: Arial; mso-bidi-font-family: Arial } DIV.Section1 {
page: Section1 }
Blue,
Ah, you're just trying to start trouble on the list-serve aren't you!
Didn't we just have a discussion about why not to use QUICK when QTP or
QUIZ will do (and I was on the side of the QUICK solution)?
Cordially,
David Morrison
McBride Electric
-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com]On Behalf Of Robert Edis
Sent: Wednesday, July 28, 2004 1:48 PM
To: PowerHouse List
Subject: RE: Calculating Median
Why do it in Quick? Then you have looping, designer files, and temporary
variables available for the logic. No subfile is required and all
processing can be done in designer procedures.
Added bonus is that user can run screen and get median value any time they
choose without MIS getting involved.
Blue
David Morrison - Corporate <dmorrison@mcbrideelectric.com> wrote:
Blair,
How about something like this:
Use a QTP program that does the following:
1. First request: Sort the data on the value that you're looking to get
the median for. Output records to a sub-file at that value, outputting the
value and COUNT RESET. Put COUNT NORESET into a global temporary item.
2. Second request: Read the sub-file created in the previous step (they're
already sorted). Accumulate the total of the counted field in each record.
Compare that to the global tempor! ary; when it reaches 1/2 of that total,
you have the median value.
David Morrison
McBride Electric
-----Original Message-----
From: powerh-l-admin@lists.sowder.com
[mailto:powerh-l-admin@lists.sowder.com] On Behalf Of Blair Combs
Sent: Wednesday, July 28, 2004 12:23 PM
To: Powerhouse Listserv
Subject: Calculating Median
Does anyone ha! ve an idea on how to calculate the Median for a list of
numbers using Quiz?
Blair Combs
Idaho State University
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.sowder.com
Subscribe: "subscribe" in message body to
powerh-l-request@lists.sowder.com
Unsubscribe: "unsubscribe " in message body to
powerh-l-request@lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.sowder.com
Subscribe: "subscribe" in message body to
powerh-l-request@lists.sowder.com
Unsubscribe: "unsubscribe " in message body to
powerh-l-request@lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
This message may contain privileged and/or confidential information.
If you have received this e-mail in error or are not the intended
recipient, you may not use, copy, disseminate or distribute it; do not open
any attachments, delete it immediately from your system and notify the
sender promptly by e-mail that you have done so. Thank you.
This message and any attachments (the "message") is
intended solely for the addressees and is confidential.
If you receive this message in error, please delete it and
immediately notify the sender. Any use not in accord with
its purpose, any dissemination or disclosure, either whole
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message.
BNP PARIBAS (and its subsidiaries) shall (will) not
therefore be liable for the message if modified.
---------------------------------------------
Ce message et toutes les pieces jointes (ci-apres le
"message") sont etablis a l'intention exclusive de ses
destinataires et sont confidentiels. Si vous recevez ce
message par erreur, merci de le detruire et d'en avertir
immediatement l'expediteur. Toute utilisation de ce
message non conforme a sa destination, toute diffusion
ou toute publication, totale ou partielle, est interdite, sauf
autorisation expresse. L'internet ne permettant pas
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce
message, dans l'hypothese ou il aurait ete modifie.