on 08-25-2014 10:36 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
Hi Senoy,
Try to use SUM(LastPeriods(12,...)) http://technet.microsoft.com/en-us/library/ms145588.aspx
Vadim
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
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.