I have a budget file with several columns, but the main colu...

[JUAN ORTIZ]

I have a budget file with several columns, but the main columns are: Sales and Gross Profit (0 to 100). and it looks like this:

Date,GP,Sales

1/12021,30,100000

1/2/2021,25,500000

1/3/2021,20,400000

I am trying to calculate Total Gross Profit which should follow this formula, for every row or record: GP/100*Sales

This would result on a GP of 235,000 or 23.5%

But, when I run my report, WebFocus calculates like this:

sum(GP) / 100 * sum(Sales),

and the result is:

750,000 or 75%

in other words, calculation occurs at the aggregate level, not at the record level which is of course too late. This is my code:

TABLE FILE DAILYBUDGET

SUM

COMPUTE TRUE_GP/D12.2 = BUDGETDAILYSALES * BUDGETGP / 100;

BUDGETGP/P21 AS BUDGETGP

BUDGETDAILYSALES/P32 AS BUDGETDAILYSALES

WHERE

(OFFICE EQ TORONTO ) AND ( BUDGETDAY_YEAR EQ 2021 );

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

ENDSTYLE

END

[David Beagan]

It seems like DEFINE instead of COMPUTE should work for you, like this:

DEFINE FILE DAILYBUDGET

TRUE_GP/D12.2 = BUDGETDAILYSALES * BUDGETGP / 100;

END

 

TABLE FILE DAILYBUDGET

SUM

TRUE_GP

BUDGETGP/P21 AS 'BUDGETGP'

BUDGETDAILYSALES/P32 AS 'BUDGETDAILYSALES'

WHERE

(OFFICE EQ 'TORONTO' ) AND ( BUDGETDAY_YEAR EQ 2021 );

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLEMBEDIMG ON

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,

$

ENDSTYLE

END

[Martin Yergeau]

Juan,

The major distinction between a DEFINEd and a COMPUTEd field is as follow:

DEFINE

 

Perform on each read rows from the source file

 

COMPUTE

 

Perform against the internal WF matrix data

Perform on filtered/selected rows : after the WHERE clause is applied

Perform on aggregation : based on the BY fields