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     [Solved] recompute field
Go
New
Search
Notify
Tools
Reply
  
[Solved] recompute field
 Login/Join
 
Member
posted
Hi I'm new to this forum and mostly a novice in InfoAssist. Im I creaded to Compute field. One ( Compute_1 ) that display a location monthly objective and the second that is doing a division ( Invoice_AMOUNT / Compute_1 ) on the line the calculation works but when I apply a Recompute both the total of the Compute_1 and the Compute_2 does work. How can I fix this. Here is the code that I'm using we are using version 7
Thank you in helping a newbie


-*COMPONENT=Define_FCT_SALE_CLUSTER
DEFINE FILE FCT_SALE_CLUSTER
Define_1/D12=IF FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_CODE EQ 'E' THEN 50000 ELSE IF FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_CODE EQ 'F' THEN 30000 ELSE 0 ;
ORDERTYPEDEF/A20=IF FCT_SALE_CLUSTER.FCT_SALE.DETAIL_SHIP_TYPE EQ 'H' THEN 'TRANSFERT' ELSE IF FCT_SALE_CLUSTER.FCT_SALE.DETAIL_SHIP_TYPE EQ 'I' THEN 'TRANSFERT' ELSE IF FCT_SALE_CLUSTER.FCT_SALE.DETAIL_SHIP_TYPE EQ 'O' THEN 'TRANSFERT' ELSE 'VENTE' ;
OBJECTIF/D12.2=IF FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_CODE EQ 'E' THEN 500000 ELSE 0 ;
END
TABLE FILE FCT_SALE_CLUSTER
SUM FCT_SALE_CLUSTER.FCT_SALE.INVOICE_AMOUNT
FCT_SALE_CLUSTER.FCT_SALE.MARGIN_LANDED
COMPUTE PCT_PB/D12.1=( FCT_SALE_CLUSTER.FCT_SALE.MARGIN_LANDED / FCT_SALE_CLUSTER.DIM_ORDER.INVOICE_AMOUNT ) * 100 ;
COMPUTE Compute_1/D12.2=IF SUM.FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_CODE EQ 'E' THEN (500000) ELSE IF SUM.FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_CODE EQ 'F' THEN (300000) ELSE IF FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_CODE EQ 'J' THEN (10000) ELSE 0;
COMPUTE Compute_2/D12.2=( FCT_SALE_CLUSTER.FCT_SALE.INVOICE_AMOUNT / TOT.Compute_1)*100;
BY FCT_SALE_CLUSTER.DIM_BRANCH.DIVISION_NAME RECOMPUTE
BY FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_NAME
WHERE FCT_SALE_CLUSTER.DIM_DATE.YEAR EQ 2016;
WHERE FCT_SALE_CLUSTER.DIM_BRANCH.BRANCH_CODE EQ 'E' OR 'F' OR 'J';
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET SQUEEZE ON
ON TABLE SET HTMLCSS ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
INCLUDE=IBFS:/CFG/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
ENDSTYLE
END

-RUN

{status:'SUCCESS',hasstatus:true,exception:[]}

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 1 | Registered: December 10, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Karl:
I'm unclear what you're asking. Is there any way for you to show us the problem with a sample table in ibisamp app directory so we can try it? Also, explain what you're seeing and what you'd rather see.


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
This is one of the cases where I'd love to jump in and help because it's pure FOCUS and not html or javascript or whatever - but I'm not prepared to do so when the code isn't laid out neatly with the irrelevant bits removed.

It's much easier if you remove all the stuff to do with holding, styling etc, make a space between the table and the define, and most importantly, remove the fully-qualified field names. Leave just the field name itself and not the file and segment name.

... geez, I'm grumpy already and its only 9 in the morning ...


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Here you go George:

 

DEFINE FILE FCT_SALE_CLUSTER
Define_1/D12=IF BRANCH_CODE EQ 'E' THEN 50000 ELSE IF BRANCH_CODE EQ 'F' THEN 30000 ELSE 0 ;
ORDERTYPEDEF/A20=IF DETAIL_SHIP_TYPE EQ 'H' THEN 'TRANSFERT' ELSE IF DETAIL_SHIP_TYPE EQ 'I' THEN 'TRANSFERT' ELSE IF DETAIL_SHIP_TYPE EQ 'O' THEN 'TRANSFERT' ELSE 'VENTE' ;
OBJECTIF/D12.2=IF BRANCH_CODE EQ 'E' THEN 500000 ELSE 0 ;
END

TABLE FILE FCT_SALE_CLUSTER
SUM INVOICE_AMOUNT MARGIN_LANDED
COMPUTE PCT_PB/D12.1=( MARGIN_LANDED / INVOICE_AMOUNT ) * 100 ;
COMPUTE Compute_1/D12.2=IF SUM.BRANCH_CODE EQ 'E' THEN (500000) ELSE IF SUM.BRANCH_CODE EQ 'F' THEN (300000) ELSE IF BRANCH_CODE EQ 'J' THEN (10000) ELSE 0;
COMPUTE Compute_2/D12.2=( INVOICE_AMOUNT / TOT.Compute_1)*100;

BY DIVISION_NAME RECOMPUTE
BY BRANCH_NAME

WHERE YEAR EQ 2016;
WHERE BRANCH_CODE EQ 'E' OR 'F' OR 'J';
END



WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
Much better!

A couple of things jump out at me right away - and I haven't even had my coffee yet.

1) I don't like SUM.BRANCH_CODE one bit. SUM is a Verb in WF and I personally have never seen it as a prefix operator

2) I don't understand why you are trying to equate a summed value with a string

3) Your Define_1 appears to perform the same function as your Compute_1 is intended to do - although it's important to understand that DEFINES operate on data as it enters the report matrix and COMPUTES operate after the matrix has been constructed

4) Why do you need the RECOMPUTE? But if it is necessary, the correct syntax is ON DIVISION_NAME RECOMPUTE

I think you would get closer to what you are looking for with:

DEFINE FILE FCT_SALE_CLUSTER
DEFINE_1/D12=IF BRANCH_CODE EQ 'E' THEN 50000 ELSE IF BRANCH_CODE EQ 'F' THEN 30000 ELSE 0 ;
ORDERTYPEDEF/A20=IF DETAIL_SHIP_TYPE EQ 'H' THEN 'TRANSFERT' ELSE IF DETAIL_SHIP_TYPE EQ 'I' THEN 'TRANSFERT' ELSE IF DETAIL_SHIP_TYPE EQ 'O' THEN 'TRANSFERT' ELSE 'VENTE' ;
OBJECTIF/D12.2=IF BRANCH_CODE EQ 'E' THEN 500000 ELSE 0 ;
END

TABLE FILE FCT_SALE_CLUSTER
SUM
DEFINE_1 NOPRINT
SUM INVOICE_AMOUNT 
MARGIN_LANDED 
COMPUTE PCT_PB/D12.1=( MARGIN_LANDED / INVOICE_AMOUNT ) * 100 ;
COMPUTE Compute_2/D12.2=( INVOICE_AMOUNT / DEFINE_1)*100;

BY DIVISION_NAME 
BY BRANCH_NAME

WHERE YEAR EQ 2016;
WHERE BRANCH_CODE EQ 'E' OR 'F' OR 'J';
END


By using the multi-verb request you will get the total of DEFINE_1 (lousy variable name btw) that you can use further down in your calculation.

If I understand what you are trying to achieve, no RECOMPUTE is necessary.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
Just a quick clarification on the prefix operators. SUM is a prefix operator. If using the code you might use
COUNT field SUM.field.

Here is a list from the WebFOCUS 8105 reporting language manual:
The following prefix operators are supported for numeric fields:
◾ASQ.
◾AVE.
◾CNT.
◾FST.
◾LST.
◾MAX.
◾MIN.
◾SUM.

The following prefix operators are supported for alphanumeric fields:
◾FST.
◾LST.
◾MAX.
◾MIN.
◾SUM. (means LST. if SUMPREFIX=LST or FST. if SUMPREFIX=FST)


Reference: Usage Notes for Summary Prefix Operators:

◾COLUMN-TOTAL does not support prefix operators.
◾Prefix operators PCT., RPCT., AND TOT. are not supported.
◾Double prefix operators (such as PCT.CNT.) are not supported.
◾When an ACROSS field is used in the request, the same field name displays over multiple columns (ACROSS groups) in the report output. A prefix operator applied to such a field on a summary line is applied to all of those columns.
◾The SUM. prefix operator produces the same summary values as a summary phrase with no prefix operator.
◾SUMMARIZE and RECOMPUTE apply the calculations defined in the associated COMPUTE command to the summary values. Therefore, in order to perform the necessary calculations, the SUMMARIZE or RECOMPUTE command must calculate all of the fields referenced in the COMPUTE command.
◾If the same field is referenced by more than one summary operation with different prefix operators at each level, the default grand total (one produced without an ON TABLE summary option command) applies the operation specified by the first operator used in the report request (the left-most sort field in the output).

Thank you for participating in the Focal Point Forum.

Kindest regards,
Tamra Colangelo
Focal Point Moderator - Information Builders Inc.
* Summit 2016 – June 13-17 in Reno, Nevada  - http://www.informationbuilders.com/events/summit


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] recompute field

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