Logic to accumulate months

[Former Member]

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

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Vadim,

Thank you for your reply.

So, I don't understand your point about the division. Why's the recommendation?

Plese find the atachment below.

Thanks.

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Right,

But the question is that I need this Avarage to use in another calculation.

Understand your point, but I think that is just a way to generate a value, independent of the formula.

I'll use this and a lot of others scripts to get a simulation.

Thanks

Regards

Lucas Costa

[former_member186338 · Accepted solution]

Then, please, explain how you want to use Avarage in another calculation? May be it's also possible to calculate the result using Dimension formula. Or some other possible options.

B.R. Vadim

[former_member186338 · Accepted solution]

To get YTD values in script you may use LOOKUP:

*LOOKUP TEST

*DIM T:ACCOUNT="TON"

*DIM C:ACCOUNT="COST"

*DIM MEASURES="YTD"

*ENDLOOKUP

*WHEN ACCOUNT

*IS COST

  *REC(EXPRESSION=(LOOKUP(T))/(LOOKUP(C)), ACCOUNT="AVARAGE")

*ENDWHEN

Where TEST is the Application name.

B.R. Vadim

[former_member186338 · Accepted solution]

Or:

*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN ACCOUNT

*IS COST

  *REC(EXPRESSION=([ACCOUNT].[TON],[MEASURE].[YTD])/([ACCOUNT].[COST],[MEASURE].[YTD]), ACCOUNT="AVARAGE")

*ENDWHEN

The line *XDIM_MEMBERSET MEASURES = PERIODIC is required!

[Former Member · Accepted solution]

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

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Thanks again Vadim,

Absolutelly is what I need.

I cannot test this right now, but later I give you a position if works or not.

Thank you!

[Former Member · Accepted solution]

Hi Vadim,

Could you please help?

http://scn.sap.com/thread/3220881

Regards

[kirill_gromyko]

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

[Former Member]

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

[kirill_gromyko]

Hello Lucas,

You are right, that would be different calculation. Need to think about it

Kind Regards,

Kirill