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     [CLOSED] top 10 % of records

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2007Report 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 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report 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, 2007Report 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, 2004Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2007Report 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, 2006Report 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, 2007Report 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     [CLOSED] top 10 % of records

Copyright © 1996-2020 Information Builders