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     [SOLVED] Median Calculation For Two Measures.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Median Calculation For Two Measures.
 Login/Join
 
Master
posted
The first TABLE FILE emulates the report I am working on, the next two TABLE FILES display the detail data:
TABLE FILE CAR
SUM MAX.RCOST
    MAX.DCOST
    AVE.RCOST
    AVE.DCOST
    CNT.RCOST
    CNT.DCOST
BY  COUNTRY
END
-*
TABLE FILE CAR
PRINT BODYTYPE
      RCOST
IF COUNTRY EQ ENGLAND
BY COUNTRY
BY RCOST NOPRINT
END
-*
TABLE FILE CAR
PRINT BODYTYPE
      DCOST
IF COUNTRY EQ 'W GERMANY'
BY COUNTRY
BY DCOST NOPRINT
END 
  
              MAX          MAX          AVE          AVE          RETAIL_COST  DEALER_COST
  COUNTRY     RETAIL_COST  DEALER_COST  RETAIL_COST  DEALER_COST  COUNT        COUNT      
  -------     -----------  -----------  -----------  -----------  -----------  -----------
  ENGLAND          17,850       14,940       11,330        9,463            4            4
  FRANCE            5,610        4,631        5,610        4,631            1            1
  ITALY            31,500       25,000       12,766       10,309            4            4
  JAPAN             3,339        2,886        3,239        2,756            2            2
  W GERMANY        14,123       11,000        9,247        7,795            7            7
  
  COUNTRY     BODYTYPE      RETAIL_COST                                         
  -------     --------      -----------                                         
  ENGLAND     HARDTOP             5,100
              CONVERTIBLE         8,878
              SEDAN              13,491
              SEDAN              17,850
  
  COUNTRY     BODYTYPE      DEALER_COST                                         
  -------     --------      -----------                                         
  W GERMANY   SEDAN               5,063
              SEDAN               5,800
              SEDAN               6,000
              SEDAN               8,300
              SEDAN               8,400
              SEDAN              10,000
              SEDAN              11,000


I need to add two new columns to the report, Median Retail Cost and Median Dealer Cost.

Given the detail data, the Median Retail Cost for England would be (8878 + 13491) / 2.

And the Median Dealer Cost for Italy would be 8300.

I've searched for median, and it seems to me that there were examples of one-off type median calculations, but no threads bringing median calcs/columns into a fully developed report.

Any comments, ideas, or suggestions welcomed.

This message has been edited. Last edited by: <Kathryn Henning>,




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
Hi David,

This may give a head start at calculating the median of a value. I leveraged your first TABLE FILE statement to capture base summary values:

TABLE FILE CAR
SUM
    MAX.RCOST AS 'MAX_RCOST'
    MAX.DCOST AS 'MAX_DCOST'
    AVE.RCOST AS 'AVE_RCOST'
    AVE.DCOST AS 'AVE_DCOST'
    CNT.RCOST AS 'REC_CNT'
COMPUTE C_MOD/I9 = IMOD(CNT.RCOST,2,'I9');
COMPUTE C_MEDIAN_STR/I9 = (CNT.RCOST / 2) + C_MOD;
COMPUTE C_MEDIAN_END/I9 = C_MEDIAN_STR + DECODE C_MOD (0 1 ELSE 0);
BY  COUNTRY
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HSUM FORMAT FOCUS INDEX COUNTRY
END
-RUN


With that information in place it's now possible to determine which records can be used to calculate the median(RCOST) for each COUNTRY:

JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN HSUM AS J0

TABLE FILE CAR
PRINT 
COMPUTE CTR/I9 = IF COUNTRY EQ LAST COUNTRY THEN CTR + 1 ELSE 1;
      BODYTYPE
      RCOST
COMPUTE C_RCOST_MED1/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_STR THEN RCOST ELSE MISSING;
COMPUTE C_RCOST_MED2/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_END THEN RCOST ELSE MISSING;
BY COUNTRY
BY RCOST NOPRINT
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
TYPE=REPORT, FONT='COURIER NEW', SIZE=9, BORDER=0.5, $
TYPE=TITLE, BACKCOLOR='SILVER', $
ENDSTYLE
END
-RUN


These are the detail results illustrating each pair of RCOST values (MED1 and MED2) will be used to calculate the median by COUNTRY:

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
Here's a complete sample code that calculates the median RCOST and DCOST putting all measurements together by COUNTRY.

The real challenge here is the multiple passes required on the underlying detail data for each median that needs to be calculated.

Given that your data is hosted in an Oracle database, you may consider using SQL analytics to calculate all the measurements values directly in the database and retrieve them via SQL passthru or perhaps through a database view. Performance gains may be significant when avoiding all those multiple passes over detail data.

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY

-* Base measures by country
TABLE FILE CAR
SUM
    MAX.RCOST AS 'MAX_RCOST'
    MAX.DCOST AS 'MAX_DCOST'
    AVE.RCOST AS 'AVE_RCOST'
    AVE.DCOST AS 'AVE_DCOST'
    CNT.RCOST AS 'REC_CNT'
COMPUTE C_MOD/I9 = IMOD(CNT.RCOST,2,'I9');
COMPUTE C_MEDIAN_STR/I9 = (CNT.RCOST / 2) + C_MOD;
COMPUTE C_MEDIAN_END/I9 = C_MEDIAN_STR + DECODE C_MOD (0 1 ELSE 0);
BY  COUNTRY
ON TABLE HOLD AS HSUM FORMAT FOCUS INDEX COUNTRY
END
-RUN

JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN HSUM AS J0
-RUN

-* Calculate median RCOST
TABLE FILE CAR
PRINT
COMPUTE CTR/I9 = IF COUNTRY EQ LAST COUNTRY THEN CTR + 1 ELSE 1; NOPRINT
COMPUTE C_RCOST_MED1/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_STR THEN RCOST ELSE MISSING;
COMPUTE C_RCOST_MED2/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_END THEN RCOST ELSE MISSING;
BY COUNTRY
BY RCOST NOPRINT
ON TABLE HOLD AS HRCOST
END

TABLE FILE HRCOST
SUM
COMPUTE C_RCOST_MEDIAN/D10.2 MISSING ON = (C_RCOST_MED1 + C_RCOST_MED2) / 2;
BY COUNTRY
ON TABLE HOLD AS HRCOSTMED FORMAT FOCUS INDEX COUNTRY
END
-RUN

-* Calculate median DCOST
TABLE FILE CAR
PRINT
COMPUTE CTR/I9 = IF COUNTRY EQ LAST COUNTRY THEN CTR + 1 ELSE 1; NOPRINT
COMPUTE C_DCOST_MED1/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_STR THEN DCOST ELSE MISSING;
COMPUTE C_DCOST_MED2/D10.2 MISSING ON = IF CTR EQ HSUM.C_MEDIAN_END THEN DCOST ELSE MISSING;
BY COUNTRY
BY DCOST NOPRINT
ON TABLE HOLD AS HDCOST
END

TABLE FILE HDCOST
SUM
COMPUTE C_DCOST_MEDIAN/D10.2 MISSING ON = (C_DCOST_MED1 + C_DCOST_MED2) / 2;
BY COUNTRY
ON TABLE HOLD AS HDCOSTMED FORMAT FOCUS INDEX COUNTRY
END
-RUN

-* Put all measures together
JOIN CLEAR *
JOIN COUNTRY IN HSUM TO COUNTRY IN HRCOSTMED AS J0
JOIN COUNTRY IN HSUM TO COUNTRY IN HDCOSTMED AS J1
-RUN

TABLE FILE HSUM
PRINT
    MAX_RCOST
    MAX_DCOST
    AVE_RCOST
    AVE_DCOST
    REC_CNT
    C_RCOST_MEDIAN
    C_DCOST_MEDIAN
BY COUNTRY
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
TYPE=REPORT, FONT='COURIER NEW', SIZE=9, BORDER=0.5, $
TYPE=TITLE, BACKCOLOR='SILVER', $
ENDSTYLE
END
-RUN




Hope this helps. Smiler

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Master
posted Hide Post
Thank you njsden, indeed you are assisting with making this thread a comprehensive example of bringing median cals into a fully developed report.
quote:
you may consider using SQL analytics to calculate

Thanks for the reminder on this. In my present case my input dataset is a flat file, created by extracts from Netezza, which is my starting point.
quote:
real challenge here is the multiple passes required on the underlying detail data for each median that needs to be calculated.

That is what I was thinking too, so it really helped to hear someone else's take.
Incorporating your technique, and median calculations presented by the FocWizards on another thread, here is what I have, that I think will work fine:
-* Focexec: ExMedian
-* Purpose: Example of calculating a median, and reporting it along with other summary type calculations.
-*
-* Environmental Settings.
-*
SET PAGE = NOPAGE
SET ONLINE-FMT = STANDARD
SET LINES = 99999
SET PANEL = 200
-*
-* Calculate and store the median RetailCost.    
-*
TABLE FILE CAR
SUM     CNT.BODYTYPE
COMPUTE M1/I4=INT((CNT.BODYTYPE + 1)/2);
COMPUTE M2/I4=INT((CNT.BODYTYPE + 2)/2);
BY      COUNTRY
-*
PRINT
COMPUTE BYFIELDS/A10 = COUNTRY;
COMPUTE XLIST/I5     = IF BYFIELDS EQ LAST BYFIELDS THEN XLIST + 1 ELSE 1;
COMPUTE CRCOST/D12.2 = IF M1 NE M2
                        THEN (LAST RCOST + RCOST)/2 ELSE RCOST; AS 'MEDIAN_RCOST'
BY      COUNTRY
BY      RCOST
-*
ON TABLE HOLD AS HLDRCMED FORMAT FOCUS INDEX COUNTRY
WHERE TOTAL XLIST EQ M2
END
-RUN
-*
-* Calculate and store the median DealerCost.    
-*
TABLE FILE CAR
SUM     CNT.BODYTYPE
COMPUTE M1/I4=INT((CNT.BODYTYPE + 1)/2);
COMPUTE M2/I4=INT((CNT.BODYTYPE + 2)/2);
BY      COUNTRY
-*
PRINT
COMPUTE BYFIELDS/A10 = COUNTRY;
COMPUTE XLIST/I5     = IF BYFIELDS EQ LAST BYFIELDS THEN XLIST + 1 ELSE 1;
COMPUTE CDCOST/D12.2 = IF M1 NE M2
                        THEN (LAST DCOST + DCOST)/2 ELSE DCOST; AS 'MEDIAN_DCOST'
BY      COUNTRY
BY      DCOST
-*
ON TABLE HOLD AS HLDDCMED FORMAT FOCUS INDEX COUNTRY
WHERE TOTAL XLIST EQ M2
END
-RUN
-*
JOIN COUNTRY IN CAR TO COUNTRY IN HLDRCMED AS J1
JOIN COUNTRY IN CAR TO COUNTRY IN HLDDCMED AS J2
-*
TABLE FILE CAR
SUM MAX.RCOST
    MAX.DCOST
    AVE.RCOST
    AVE.DCOST
    CNT.RCOST
    CNT.DCOST
    MEDIAN_RCOST    AS 'Median, Retail Cost'
    MEDIAN_DCOST    AS 'Median, Dealer Cost'
BY  COUNTRY
END 

  
              MAX          MAX          AVE          AVE          RETAIL_COST  DEALER_COST    Median          Median      
  COUNTRY     RETAIL_COST  DEALER_COST  RETAIL_COST  DEALER_COST  COUNT        COUNT           Retail Cost     Dealer Cost
  -------     -----------  -----------  -----------  -----------  -----------  -----------    ------------    ------------
  ENGLAND          17,850       14,940       11,330        9,463            4            4       11,184.50        9,310.50
  FRANCE            5,610        4,631        5,610        4,631            1            1        5,610.00        4,631.00
  ITALY            31,500       25,000       12,766       10,309            4            4        6,820.00        5,660.00
  JAPAN             3,339        2,886        3,239        2,756            2            2        3,239.00        2,756.00
  W GERMANY        14,123       11,000        9,247        7,795            7            7        9,097.00        8,300.00  
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
Ahhh! Multi-verb requests. They really simplify the process avoiding unnecessary intermediate HOLD file creation. Very nice!



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report 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     [SOLVED] Median Calculation For Two Measures.

Copyright © 1996-2020 Information Builders