Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] top 10 % of records
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] top 10 % of records
 Login/Join
 
Silver Member
posted
Has anyone done this before?
I need to return only the top 10 % of distinct site ID's based on their "volume" (of activities) since the beginning of the year.

Thank you for any help!

This message has been edited. Last edited by: Kerry,


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
For that to work, you first need to know how many there are. Then divide that amount by 10 and use that as your recodr limit.


GamP

- Using AS 8.2.01 on Windows 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
Would you use the first TABLE FILE request to get the number of records and then continue with the rest? I thought there may be a more "compact" technique using the TOTAL lines, etc...


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
Something like this?

TABLE FILE CAR
PRINT *
ON TABLE HOLD AS CARHOLD
END
-*
-SET &NUMLINES = &LINES*.10;

TABLE FILE CAR
PRINT * 
BY HIGHEST &NUMLINES.EVAL MODEL NOPRINT
END


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Guys, this one is related volume. May not be counts.

How aboput this. It takes the top n pct, + any thay flow over the limit.
-SET &PCT_AMT = 50 ;
SET ASNAMES = ON
TABLE FILE CAR
SUM SALES AS TOTSALES
SUM SALES
BY CAR
ON TABLE HOLD AS TMP_TOP
END

TABLE FILE TMP_TOP
PRINT SALES
      COMPUTE INCSALES/I6   = LAST INCSALES + SALES ;
      COMPUTE PCTSALES/D6.2 = INCSALES / TOTSALES * 100 ;
      COMPUTE Filter/A1     = IF PCTSALES LT &PCT_AMT THEN 'Y' ELSE 
                              IF LAST Filter EQ 'Y' AND
                                 LAST PCTSALES LT &PCT_AMT
                              THEN 'Y' ELSE 'N' ;
BY HIGHEST SALES
BY CAR
WHERE TOTAL Filter EQ 'Y'
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Similar to Waz's post, something like this might work. Your WHERE clauses would filter on your date column. I had to take the top 15% (PERCENTILE > 85) in order to get more than one country to show in the output.

SET HOLDLIST = PRINTONLY

DEFINE FILE CAR
 SITE_SALES/D7 = SALES ;
END

TABLE FILE CAR
 SUM SITE_SALES
 BY COUNTRY

 SUM SALES/D7
 BY COUNTRY
 BY CAR
 WHERE COUNTRY NE 'FRANCE';
 ON TABLE HOLD
END

TABLE FILE HOLD
 SUM COMPUTE TOP_SALES/D7 = MAX.SITE_SALES ; NOPRINT

 PRINT
  COUNTRY AS 'Site'
  CAR     AS 'ID'
  SALES   AS 'Sales'
  COMPUTE PERCENTILE/D6.1 = SITE_SALES / TOP_SALES * 100 ; NOPRINT
  COMPUTE TOTALTEXT/A25 = 'TOTAL ' | COUNTRY ; NOPRINT
 BY HIGHEST SITE_SALES NOPRINT
 BY HIGHEST SALES      NOPRINT
 ON SITE_SALES SUBFOOT
  "<TOTALTEXT<SITE_SALES"
  "PERCENTILE<PERCENTILE"
  " <+0> "
 WHERE TOTAL (PERCENTILE GT 85)
 ON TABLE SET STYLE *
  TYPE=SUBFOOT, HEADALIGN=BODY, $
  TYPE=SUBFOOT, ITEM=1, COLSPAN=2, $
  TYPE=SUBFOOT, ITEM=2, JUSTIFY=RIGHT, $
 ENDSTYLE
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
What about IN-GROUPS-OF ... TILES? Check out the doc on this. I've never used it but it sounds like it could work.


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, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This code will tile MODEL sales in deciles, and you can limit the output to the top decile (top 10%) by adding TOP 1 to the IN-GROUPS-OF statement. But I have never understood how the tiles are calculated. This code generates only 8 tiles, not 10. Maybe someone else can shed some light on this.

SET BYDISPLAY = ON

TABLE FILE CAR
 SUM 
  COUNTRY
  CAR
  MODEL
  SALES/D7
 BY HIGHEST SALES NOPRINT
 IN-GROUPS-OF 10 TILES
-* IN-GROUPS-OF 10 TILES TOP 1
 BY MODEL   NOPRINT
 BY CAR     NOPRINT
 BY COUNTRY NOPRINT
 ON TABLE COLUMN-TOTAL
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] top 10 % of records

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.