SAP BPC NW 10.1 Cross-Dimensional member formula

[Former Member]

Hi:

I am trying to calculate a cross-dimensional member formula using the LastPeriods MDX function.  Although the final formula needs to be longer, here is the first test section that I have tried:

SUM(LastPeriods(12,([TIME].CURRENTMEMBER)),[ACCOUNT].[ZLB1169],[COST_CENTER].[CC_ALLMFG])

The formula saves and calculates, but instead of the expected result, the member is populated with the monthly value from 12 periods earlier, as if I were using a LAG function.

Simple question:  can the LastPeriods function tolerate a multiple dimension reference?  If I take out the cost center member, the formula returns an expected amount.

Ultimately, I need to add two more SUM calcs to this formula with different ACCOUNT/COST_CENTER references...and divide that total by 12 to get an average.

[former_member186338]

Hi Jack,

What is the ACCTYPE of [ACCOUNT].[ZLB1169]?

What is the model storage type?

Vadim

[Former Member]

Hi Vadim:

That account is an EXP, and the model is a YTD storage, however I need to view the results as Periodic because summing the YTD values of an EXP item isn't correct.  I have other calcs for both EXP and LEQ items that sum over a number of periods in the periodic measure that work correctly--they just don't involve a cross-dimensional product.

I think this is the only cross-dim member formula that the client needs, so I may just attach it to default logic and limit the REC statement as much as possible (i.e. no need to store it to every DataSrc, Flow and Currency dimension member).  I just need to be careful to make sure that the returned value for a rollup Entity member is correct, which might be a problem.

Thank you for your help.

[former_member186338]

"I think this is the only cross-dim member formula that the client needs, so I may just attach it to default logic and limit the REC statement as much as possible (i.e. no need to store it to every DataSrc, Flow and Currency dimension member).  I just need to be careful to make sure that the returned value for a rollup Entity member is correct, which might be a problem." - never try to replace member formula by script logic calculation - different things...

Can you explain what measure you are using?

Vadim

[Former Member]

Great article link.  I had come to essentially the same conclusion....calculate the cross product amounts in logic, store those amounts to a temporary stat account, and then use that account in the member formula to calculate the desired ratio correctly.

Basically, the client wants to see a driver for Average Cost of Sales.  This value is obtained by summing three EXP account type rollups:

ZLB1169 - Operational Costs  (This is only at the Operations Cost Center level, don't want SG&A)

ZLB1164 - Cost of Goods at Standard (at the Total Cost Center level)
ZLB1165 - Other Manufacturing Costs (at the Total Cost Center level)

I need to sum these amounts over the previous twelve months and then divide the total by 12.  By the way, Cost Center is NOT the Entity dimension....Profit Center is Entity.

My thought is...as per your excellent article...to sum these amounts via script logic for each month and store the result to a holding account (e.g. [COS]) and then calculate the measure via a dimension formula:

SUM(LastPeriods(12,([TIME].[CURRENTMEMBER])),[COS])/12

I will just need to limit the REC statement in the logic because I don't want pieces of these numbers stored all over the model.  Any report that displays this measure will be context-locked at the top member for the miscellaneous dimensions (Cost Center, Flow, Group, Intercompany, and Segment).  I do want this value to vary at the Entity level, so all is good.

Again, thanks for your help.

[former_member186338]

Hi Jack,

Not everything is clear... My article was written for the PERIODIC model...

In you case with YTD storage you have accumulated values since the beginning of the Year. And SUM(LastPeriods(12,... will generate strange results...

Vadim

[former_member186338]

Just look on my old post: