[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
September 26, 2013, 05:36 AM
Dave
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
September 26, 2013, 08:09 AM
TKMAN
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
September 27, 2013, 03:20 AM
Dave
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.