on 08-02-2012 8:06 PM
Hi all,
I have a requirement to implement a logic that generate the average cost of production.
This average cost is the sum of the entries of the production divide by the sum of entries in quantity(ton).
The key gere is that I need for a month the sum of prior months.
For example, assuming the calculation of APRIL, is something like:
AVARAGE = (COST (JAN,FEB,MAR,APR) / (TON (JAN,FEB,MAR,APR)
I already tried a logic using the *FOR statement for the range of months, and inside the loop I used the *ADD statement.
But with no sucess.
Thanks in advance for the help.
Lucas
Hi, Lucas,
First of all, it's not a good idea to store in the cube the results of division of one member by another member. For KPI's it's better to use dimension formulas:
For the member AVARAGE put the following formula:
([ACCOUNT].[COST],[MEASURE].[YTD])/([ACCOUNT].[TON],[MEASURE].[YTD]);SOLVE_ORDER=10
Assuming ACCOUNT is the name of Dimension with members AVARAGE, COST and TON
B.R. Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The cube data in general have to be additive, to be able to use hierarchies to sum members. In your example if you want to see the Year Total Average cost and you calculate each month by script dividing something by something - in Year Total you will get the sum of month values (definitely not what you want). In case of Dimension formula as in my previous post you will get a correct result. Dimension formulas are calculated on the fly during report refresh.
B.R. Vadim
P.S.
It's better to check for zero in the formula:
IIF
([ACCOUNT].[TON],[MEASURE].[YTD])=0,NULL,([ACCOUNT].[COST],[MEASURE].[YTD])/([ACCOUNT].[TON],[MEASURE].[YTD]));SOLVE_ORDER=10
Message was edited by: Vadim Kalinin
Thank you Vadim!
You solved my issue. Unfortunately, not all of it.
Here we work with CROP YEAR. So, we need to plan 15 month (from jan 2012 to march 2013, for example).
And the script goes well till jan 2013... Than, return zero.
If you know some way to get the YTD from the CROP YEAR... Please, Let me know.
Regards and thank you again.
Lucas
Hi, Lucas
Please explain in detail, what do you mean by CROP YEAR? Anyway, you can create a custom measure as explained here: http://scn.sap.com/people/robert.marshall/blog/2011/01/04/rolling-12-custom-measure-in-bpc-7x-versio...
B.R. Vadim
Hello Lucas,
Using the following script logic you can pre-calculate average value for each month. Using reporting capabilities (by looking at YTD values for AVG account) you can implement what you wanted.
You have 3 accounts
COST
TON
AVG
all of EXP account type.
Use the following script logic
*XDIM_MEMBERSET ACCOUNT = COST,TON
*XDIM_MEMBERSET CATEGORY = Actual
*WHEN CATEGORY
*IS Actual
*WHEN ACCOUNT
*IS COST
*REC(EXPRESSION=(%VALUE%/[ACCOUNT].[TON]), ACCOUNT = AVG)
*ENDWHEN
*ENDWHEN
*COMMIT
For reporting you can use shared axis and put Measure dimension with YTD on page axis (for second report). This way you will have AVG in the way you need it on the same sheet.
Kind Regards,
Kirill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you Kirill,
Your script will not help me. The calculation in your example, is month by month.
What I need is to sum the months before the current month.
The script need the following sequence:
JAN = JAN/JAN
FEB = SUM(JAN,FEB)/SUM(JAN,FEB)
MAR = SUM(JAN,FEB,MAR)/SUM(JAN,FEB,MAR)
APR = SUM(JAN,FEB,MAR,APR)/SUM(JAN,FEB,MAR,APR)
....
Thanks in advance.
Regards!
Lucas
User | Count |
---|---|
15 | |
4 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.