cancel
Showing results for 
Search instead for 
Did you mean: 

BPC 10 NW Member Formula

Former Member
0 Kudos

Hello Experts,

I am on SAP BPC 10 NW environment and I have a requirement to create Account Member Formula to get the last 12 period values for SALES_TOT Account

My Member Formula is as Below and it works fine

[SALES_TOT]+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(1))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(2))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(3))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(4))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(5))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(6))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(7))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(8))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(9))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(10))+([ACCOUNT].[SALES_TOT],[TIME].CURRENTMEMBER.LAG(11))

But some one suggested me that I can also use SUM MDX formula for above requirement.I wrote the below formula but it doesnt pulls any record.

I would like to use SUM formula as I have many such member formula requirement based on time lag and my formula becomes huge if I don't use it

SUM({[TIME].CURRENTMEMBER.LAG(0):[TIME].CURRENTMEMBER.LAG(11)},[ACCOUNT].[SALES_TOT])

Can anyone help me identify the issue in SUM Formula?

Thanks for your help

Regards,

Senoy

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

And by the way, your formula also works (just start with LAG(11)):

IIF([TIME].CurrentMember.Properties("LEVEL")="MONTH",SUM({[TIME].CURRENTMEMBER.LAG(11):[TIME].CURRENTMEMBER},[ACCOUNT].[SALES_TOT]),NULL);SOLVE_ORDER=10

Vadim

former_member186338
Active Contributor
0 Kudos

But I prefer:

IIF([TIME].CurrentMember.Properties("LEVEL")="MONTH",SUM(LastPeriods(12,[TIME].CURRENTMEMBER),[ACCOUNT].[SALES_TOT]),NULL);SOLVE_ORDER=10

Also working fine!

B.R. Vadim

Former Member
0 Kudos

Hello Vadim,

Thanks a lot for your time and efforts.You are genius.

Regards,

Senoy

Former Member
0 Kudos

Hello Vadim,

I have posted an issue in BPC script in below post.Please help if possible

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

Thanks,

Senoy

Former Member
0 Kudos

Hello Vadim,

I have raised a SDN post

  Table Config for Index is not Valid error for BPC

Could I please request you to help me in this issue?

Thanks in Advance.

Regards,

Senoy

Answers (1)

Answers (1)

former_member186338
Active Contributor
0 Kudos

Hi Senoy,

Try to use SUM(LastPeriods(12,...)) http://technet.microsoft.com/en-us/library/ms145588.aspx

Vadim