[SOLVED] Calulation with Across values/Limit output on row-total value?

Hi,

im getting a bit lost in trying to use RECAP, SUMMARIZE or RECOMPUTE.
I would like calculate spend value/overall spend value across orgunits.
The pseudo code is unfortunately not working...and not in the right place ..

Is it possible to limit the output on the row-total colum MYSPENDCHECK?

TABLE FILE SOME_INVOICEDATA
SUM
COMPUTE MYSPENDCHECK\I1 = IF NET_VALUE_EUR GT 0 THEN 1 ELSE 0;
-*Pseudo code -> COMPUTE MYCALC\D20.2 = IF NET_VALUE_EUR GT 0 THEN (NET_VALUE_EUR / (ROW-TOTAL from (NET_VALUE_EUR)) ) * 100; AS 'ORGUnitsPercentageFromSpend'
NET_VALUE_EUR
BY PARTNER_ID
BY PARTNER_NAME
ACROSS ORGNAME AS 'ORGUNIT'
ROW-TOTAL AS 'OVERALL'
-*not sure how to address the row-total colum of MYSPENDCHECK not working is this --> WHERE TOTAL MYSPENDCHECK GT 3;
WHERE PARTNER_ID IN FILE MYUMSNET;
ON TABLE SET PAGE-NUM OFF
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END

Thank you for any ideas!

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

-----------------------------
PROD WF 7.7.02, PMF 5.2.3, MSQL 2005
QA WF 7.7.02, PMF 5.2.3, MSQL 2005
Primarily self-service; adapters: SAP BW, SAP R/3, SQL Server, FlexEnable
Windows, all output

Posts: 22 | Location: Germany | Registered: December 17, 2009

Dave

Master

posted

Hide Post

Gutentag, TKMAN

I understand what you want.

You could use:

COMPUTE MYCALC\D20.2 = IF NET_VALUE_EUR GT 0 THEN ( NET_VALUE_EUR / ( TOT.NET_VALUE_EUR ) ) * 100;

But personally I don't like it because I have no control over the TOT. Sometimes I need a TOT within certain BY's fields and not the grand-total.

I usually make sure I have a seperated ( pre calculated ) column with the total I like to use do such calculations.

TABLE FILE CAR
SUM SALES AS 'TOTAL_SALES'
SUM SALES AS 'CAR_SALES'
BY CAR
ON TABLE HOLD AS X
END

TABLE FILE X
SUM TOTAL_SALES
    CAR_SALES
    COMPUTE PART/D12.2 = ( CAR_SALES / TOTAL_SALES ) * 100;
BY CAR
ON TABLE PCHOLD FORMAT HTML
END

You might want to lookup 'WITHIN' aswell in the documentation. ( or click 'options' on a SUM field an that a look at the third tab. )

g'luck,
Dave

_____________________
WF: 8.0.0.9 > going 8.2.0.5

Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010

Ignored post by Dave posted

Show Post

TKMAN

Member

posted

Hide Post

Hi Dave,

thanks for your suggestion.

I tried this

TABLE FILE SOME_INVOICEDATA
SUM NET_VALUE_EUR AS 'NET_VALUE'
RPCT.NET_VALUE_EUR/D20.2% AS '%'
....

this worked fine for the percentage calc.

Im still fiddling with the WHERE CLAUSE on the row-total of that virtual field...is there a way to recalulate that before the final output starts?

-----------------------------
PROD WF 7.7.02, PMF 5.2.3, MSQL 2005
QA WF 7.7.02, PMF 5.2.3, MSQL 2005
Primarily self-service; adapters: SAP BW, SAP R/3, SQL Server, FlexEnable
Windows, all output

Posts: 22 | Location: Germany | Registered: December 17, 2009

Ignored post by TKMAN posted

Show Post

Dave

Master

posted

Hide Post

RPCT works...

but it sometimes gives me small 'rounding'-error with makes the total end up at 99.9% or 100.1%. :-\

If you do it the way I posted you could add the WHERE CLAUSE there.

Good luck,
Dave

_____________________
WF: 8.0.0.9 > going 8.2.0.5

Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010

Ignored post by Dave posted

Show Post

Please Wait. Your request is being processed...