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,

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.

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 ...

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

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.

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