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] Problem Totalling Computed Fields

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Problem Totalling Computed Fields
 Login/Join
 
Gold member
posted
Please consider the code below. I am calculating the total revenue as the product of retail_price and units_sold for a particular store code "K1". I defined a margin percentage of 10% for item code "B10" and 20% for all other items. Total Revenue for this store should be 56.08, and total margin should be 9.93.


 
DEFINE FILE SALES
MARGIN_PCT= IF PROD_CODE = 'B10' THEN 0.1 ELSE .2;
END
 
TABLE FILE SALES
 
SUM 
COMPUTE NUMBER_OF_ITEMS/I5 = CNT.UNIT_SOLD; AS '# of Items'
COMPUTE NUMBER_OF_PURCH/I5 = SUM.UNIT_SOLD; AS 'Total Units Sold '
COMPUTE REVENUE/D12.2 = UNIT_SOLD * RETAIL_PRICE ; AS 'Total Revenue'
COMPUTE	TOTAL_MARGIN = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT; AS 'Total Margin'
BY STORE_CODE AS 'Store ID'
BY PROD_CODE AS 'Item ID'
BY RETAIL_PRICE AS 'Retail Price'
BY MARGIN_PCT AS 'Magin %'
WHERE STORE_CODE='K1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END


Now if you comment out the last 3 BY Fields for Prod_Code, Retail Price, and Margin_PCT and execute , you will notice that the total revenue and total margin calculation are not correct.

 
DEFINE FILE SALES
MARGIN_PCT= IF PROD_CODE = 'B10' THEN 0.1 ELSE .2;
END
 
TABLE FILE SALES
 
SUM 
COMPUTE NUMBER_OF_ITEMS/I5 = CNT.UNIT_SOLD; AS '# of Items'
COMPUTE NUMBER_OF_PURCH/I5 = SUM.UNIT_SOLD; AS 'Total Units Sold '
COMPUTE REVENUE/D12.2 = UNIT_SOLD * RETAIL_PRICE ; AS 'Total Revenue'
COMPUTE	TOTAL_MARGIN = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT; AS 'Total Margin'
BY STORE_CODE AS 'Store ID'
-*BY PROD_CODE AS 'Item ID'
-*BY RETAIL_PRICE AS 'Retail Price'
-*BY MARGIN_PCT AS 'Magin %'
WHERE STORE_CODE='K1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
 


I understand that webfocus is now taking (1.99 + .49) * 42 which equals 104.16 for total revenue ,
and taking (104.16 * .30) which equals for 31.25 . However, these amounts are obviously incorrect based on the data we have.

How could one solve this problem so that the total revenue & margin for this store is calculated correctly WITHOUT using a HOLD table?
Your help is greatly appreciated

This message has been edited. Last edited by: Joey Sandoval,




Prod/Dev: WebFOCUS 8.0.08 on Windows Server 2008/Tomcat , WebFOCUS DevStudio 8.0.08 on Windows 7 Pro


 
Posts: 94 | Location: Austin, TX | Registered: August 08, 2012Report This Post
Virtuoso
posted Hide Post
You're using COMPUTE within the table body.
These computed fields will only be calculated AFTER completing the internal matrix, ie after having summed and sorted. Thus COMPUTE will use the aggregated values.
The obvious solution to this is to do your math in DEFINE, which will calculate with the individual records and will produce the correct result:
DEFINE FILE SALES
MARGIN_PCT/D6.2    = IF PROD_CODE = 'B10' THEN 0.1 ELSE 0.2;
NUMBER_OF_ITEMS/I5 = 1;
NUMBER_OF_PURCH/I5 = UNIT_SOLD;
REVENUE/D12.2      = UNIT_SOLD * RETAIL_PRICE ;
TOTAL_MARGIN/D12.2 = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT;
END

TABLE FILE SALES
SUM   
      NUMBER_OF_ITEMS    AS '# of Items'
      NUMBER_OF_PURCH    AS 'Total Units Sold '
      REVENUE            AS 'Total Revenue'
      TOTAL_MARGIN       AS 'Total Margin'

BY STORE_CODE AS 'Store ID'
-*BY PROD_CODE AS 'Item ID'
-*BY RETAIL_PRICE AS 'Retail Price'
-*BY MARGIN_PCT AS 'Margin %'

WHERE STORE_CODE EQ 'K1';

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
Try this,

DEFINE FILE SALES
MARGIN_PCT= IF PROD_CODE = 'B10' THEN 0.1 ELSE .2;
REVENUE/D12.2 = UNIT_SOLD * RETAIL_PRICE ;
TOTAL_MARGIN/D12.2 = UNIT_SOLD * RETAIL_PRICE * MARGIN_PCT;
END

TABLE FILE SALES

SUM
COMPUTE NUMBER_OF_ITEMS/I5 = CNT.UNIT_SOLD; AS '# of Items'
COMPUTE NUMBER_OF_PURCH/I5 = SUM.UNIT_SOLD; AS 'Total Units Sold '
REVENUE AS 'Total Revenue'
TOTAL_MARGIN AS 'Total Margin'
BY STORE_CODE AS 'Store ID'

WHERE STORE_CODE='K1'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
Gold member
posted Hide Post
Thank you for the clarification. I was misled into thinking that this could be accomplished using computes within a sum field.




Prod/Dev: WebFOCUS 8.0.08 on Windows Server 2008/Tomcat , WebFOCUS DevStudio 8.0.08 on Windows 7 Pro


 
Posts: 94 | Location: Austin, TX | Registered: August 08, 2012Report This Post
Member
posted Hide Post
Revenue and TOTAL_MARGIN need to be defined fields. Remember computes are evaluated after all data has been aggregated, hence your margin here would be .1* the number of B10 items plus .2*the number of non B10 items. Move REVENUE and TOTAL_MARGIN to your define and life will be good.
 
Posts: 17 | Location: Colorado, USA | Registered: January 22, 2010Report 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] Problem Totalling Computed Fields

Copyright © 1996-2020 Information Builders