January 07, 2009, 01:40 PM
JOEAcross Avg
I'm stuck getting an avg for an across ROW-Total. Is this possible? I just need an average not a total.
TABLE FILE VER2
SUM
SCORE NOPRINT
BY MARKET_TEAM
BY FULLNAME
ACROSS LNUM NOPRINT
ACROSS SCOREB AS 'SCORES'
HEADING
"<Group "
FOOTING
""
WHERE Group EQ '&GROUP';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
RIGHTGAP=0.125000,
$
TYPE=DATA,
BACKCOLOR=RGB(210 210 210),
$
TYPE=DATA,
COLUMN=ROWTOTAL(*),
STYLE=BOLD,
$
TYPE=TITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=14,
COLOR='NAVY',
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
$
TYPE=ACROSSTITLE,
SIZE=9,
COLOR='WHITE',
BACKCOLOR=RGB(0 128 255),
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
STYLE=BOLD,
$
ENDSTYLE
END
January 07, 2009, 01:50 PM
FrankDutchWhat is your average summed field?
can you create an average value in a normal view (not across)
January 07, 2009, 01:58 PM
JOEThe average will be an average SCORE for each person or field FULLNAME. The original data as a score as a separate record for each person. So, I created this across query, I think with your help, thanks by the way. Now the score for each person goes across which we want. However, I want to add a final column "Avg Score" at the end. The ROW-Total function sums the data across. I use the Studio Developer 7.6.1. Is there an option that I can make the ROW-TOTAL Avgerage the score instead of sum? Thanks, Joe
January 07, 2009, 04:19 PM
susannahshort answer: nope
long answer: double verbs
several ways to do this, here's one:
TABLE FILE VER2
SUM AVE.SCORE NOPRINT
BY MARKET_TEAM
BY FULLNAME
SUM SCORE
BY MARKET_TEAM
BY FULLNAME
ACROSS LNUM
COMPUTE TOT/D12=C1;
END
-RUN
here it is on the car file;
remember the average will be whacky b/c we're not averaging at the granular level. but you'll do that, right?
"C1" is a column reference, it refers to the first column of data, not the first column of BY fields, but the first column of data.
In this case its AVE.SALES which we have cleverly NOPRINTed...
TABLE FILE CAR
SUM AVE.SALES NOPRINT BY COUNTRY
SUM SALES BY COUNTRY ACROSS CAR
COMPUTE TOT/D8=C1;
END
January 08, 2009, 01:59 PM
JOEThanks Sue. This seemed to work. The data are stickly scores for an individual. A person can have up to six scores. I'm not weighting these scores just simple average. I check some of the avg and they seemed to match. Would this be safe to use? Thanks, Joe
January 08, 2009, 06:55 PM
susannahyes joe
safe to use, as long as you are calculating the averages from source data that exists at the appropriate granularity.
if you're reading a flat file,
and if there is 1 record per MARKET, NAME, LINENUM, then you're good to go.
-s.
January 09, 2009, 01:02 PM
JOE[SOLVED] Thanks!
January 09, 2009, 02:03 PM
FrankDutchJoe
if you open the first initial message you can change the subject.
please add [solved] there!