MDX / Member Formulas nested Formulas

[ChristianSass]

Hi,

I use some Member Formulas to calculated key figures.

My first Formula Looks like this

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "1", AVG(LastPeriods(1,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "2", AVG(LastPeriods(2,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "3", AVG(LastPeriods(3,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "4", AVG(LastPeriods(4,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "5", AVG(LastPeriods(5,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "6", AVG(LastPeriods(6,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "7", AVG(LastPeriods(7,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "8", AVG(LastPeriods(8,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "9", AVG(LastPeriods(9,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "10", AVG(LastPeriods(10,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "11", AVG(LastPeriods(11,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01,

IIF([H_TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "12", AVG(LastPeriods(12,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92] ) * 0.01, NULL))))))))))))

In the 2nd formula I would like to use the result of Formula 1 and add the value of another postion but this time a add another selection. It Looks like this

[H_ACCOUNT].[KF_ONE] + ([3RD_IC].[PARENTH1].[3RD] , [H_ACCOUNT].[PARENTH1].[NODE99])

The result is the value onf KF_ONE ... Why don't I get the Value for the 2nd part?

BPC 10.1

HANA Support

and yes, I've activated MDX in spro

[ChristianSass · Accepted solution]

Hi,

I've to come back on this post. Is there an easy way to sum up all results per Periode on year Level?

The formula is based on an AST account an it calculates a values / month. I use the property "MONTHNUM" of the time Dimension to get an average value for X periods * percentage. On the "total" Level I would like to get the sum of all periods. Since underlying account is AST this is not that easy. I thougt changing the "MONTHNUM" value of e.g. 2016.TOTAL from 12 to 13 and to recalculate all values again but 1st it doesn't work and 2nd the formula is very Long.

IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "", 1,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "1", [ACCOUNT].[ACCGR_99] * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "2", AVG(LastPeriods(2,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "3", AVG(LastPeriods(3,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "4", AVG(LastPeriods(4,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "5", AVG(LastPeriods(5,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "6", AVG(LastPeriods(6,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "7", AVG(LastPeriods(7,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "8", AVG(LastPeriods(8,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "9", AVG(LastPeriods(9,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "10", AVG(LastPeriods(10,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "11", AVG(LastPeriods(11,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99]) * 0.015,IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "12", AVG(LastPeriods(12,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99] ) * 0.015, IIF([TIME].CURRENTMEMBER.PROPERTIES("MONTHNUM") = "13", AVG(LastPeriods(1,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(2,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(3,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(4,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(5,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(6,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(7,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(8,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(9,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(10,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(11,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015+AVG(LastPeriods(12,[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015) * 0.015, 1))))))))))))))

[former_member186338 · Accepted solution]

Sorry, but I am unable to understand the required logic!

Please explain calculations with some formulas (not MDX) and attach a screenshot of Excel table...

[ChristianSass · Accepted solution]

[former_member186338 · Accepted solution]

Unable to understand - do you really want to Sum average values as Year Total?

What is the business logic behind?

[ChristianSass · Accepted solution]

I knew that you would ask me about the Business logic behind .... and I've no clue ... It is a Special key figure that they always calculated like that. We used to do it in the good old query designer and worked

[former_member186338 · Accepted solution]

Sorry, but doing things without understanding the business logic is a habit of a low qualified consultant...

By the way the original formula for months can be simply:

AVG(PERIODSTODATE([TIME].[LEVEL00], [TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015;SOLVE_ORDER=10

[former_member186338 · Accepted solution]

Just some results of experiments

IIF([TIME].CURRENTMEMBER.PROPERTIES("PERIOD")="TOTAL",(

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*3.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-1),[ACCOUNT].[ACCGR_99])*2.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-2),[ACCOUNT].[ACCGR_99])*1.6032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-3),[ACCOUNT].[ACCGR_99])*1.2698773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-4),[ACCOUNT].[ACCGR_99])*1.0198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-5),[ACCOUNT].[ACCGR_99])*0.8198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-6),[ACCOUNT].[ACCGR_99])*0.6532107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-7),[ACCOUNT].[ACCGR_99])*0.5103535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-8),[ACCOUNT].[ACCGR_99])*0.3853535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-9),[ACCOUNT].[ACCGR_99])*0.2742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-10),[ACCOUNT].[ACCGR_99])*0.1742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-11),[ACCOUNT].[ACCGR_99])*0.0833333)*0.015

,AVG(PERIODSTODATE([TIME].[LEVEL00], [TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR_99])*0.015;SOLVE_ORDER=10

[former_member186338 · Accepted solution]

P.S. Do you think that the result in Year Total calculated by this formula has any business value ?

[ChristianSass · Accepted solution]

Hi Vadim,

it's very close already

I've tried to add the AVG to the TOTAL Level but there is still an error and I get a short dump in EXCEl

Any hint?

IIF([TIME].CURRENTMEMBER.PROPERTIES("PERIOD")="TOTAL",
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-0), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-1), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-2), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-3), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-4), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-5), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-6), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-7), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-8), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-9), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-10), [ACCOUNT].[ACCGR])*0.015+
AVG(PERIODSTODATE([TIME].[LEVEL00], OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-11), [ACCOUNT].[ACCGR])*0.015 ,
AVG(PERIODSTODATE([TIME].[LEVEL00],[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR])*0.015)

[former_member186338 · Accepted solution]

Sorry, don't calculate total as sum of avg - useless and slow.

Look at my formula - do you understand it?

[ChristianSass · Accepted solution]

Honestly not really. Where do get These strange looking values for multiplication from? I thought it is a mistake but maybe it's the clue bedind your formula

[former_member186338 · Accepted solution]

It's a school level arithmetic

Hint:

1 month avg: X1 (X1 - value in first month)

2 month avg: (X1+X2)/2

3 month avg: (X1+X2+X3)/3

...

12 month avg: (X1+X2+X3+...+X12)/12

5*7*8*9*11=27720

Sum=(X1*27720+X1*13860+X2*13860+X1*9240+X2*9240+X3*9240...)/27720

Sum=X1*(27720+13860+9240+...)/27720+X2*(13860+9240+...)/27720+...

Do I need to continue ?

[ChristianSass · Accepted solution]

ok, I'm lost.

0.15 was only a dummy percentage. The real percentage is 0.1731 per year

calculating the avg is okay for me

but 5*7*8*9*11 = 27720 why? and all the calcs after that I'm lost.

[former_member186338 · Accepted solution]

"calculating the avg is okay for me" - but not okay for the system

OK, back to school

You want to sum fractions:

X1/1+(X1+X2)/2+(X1+X2+X3)/3+(X1+X2+X3+X4)/4...

You need to find the minimal integer that can be divided by 1,2,3,4,...,12

This integer is: 5*7*8*9*11=27720

Then we convert the expression to the same:

X1*27720/(1*27720)+(X1+X2)*27720/(2*27720)+(X1+X2+X3)*27720/(3*27720)+(X1+X2+X3+X4)*27720/(4*27720)...

Then to:

X1*27720/27720+(X1+X2)*13860/27720+(X1+X2+X3)*9240/27720+(X1+X2+X3+X4)*6930/27720...

Then opening the brackets:

(X1*27720+X1*13860+X2*13860+X1*9240+X2*9240+X3*9240+X1*6930+X2*6930+X3*6930+X4*6930...)/27720

Then rearranging:

X1*(27720+13860+9240+6930+...)/27720+X2*(13860+9240+6930+...)/27720+X3*(9240+6930+...)/27720+X4*(6930+...)/27720+...

And we have coefficients!

[former_member186338 · Accepted solution]

P.S. As a result you can see that the value in the first month will affect the result 37 times higher then the value in the last month... is it reasonable for business?

[ChristianSass · Accepted solution]

okay, the coefficients stay constant and I only Change the percentage like this:

IIF([TIME].CURRENTMEMBER.PROPERTIES("PERIOD")="TOTAL",

(

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR])*3.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-1),[ACCOUNT].[ACCGR])*2.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-2),[ACCOUNT].[ACCGR])*1.6032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-3),[ACCOUNT].[ACCGR])*1.2698773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-4),[ACCOUNT].[ACCGR])*1.0198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-5),[ACCOUNT].[ACCGR])*0.8198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-6),[ACCOUNT].[ACCGR])*0.6532107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-7),[ACCOUNT].[ACCGR])*0.5103535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-8),[ACCOUNT].[ACCGR])*0.3853535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-9),[ACCOUNT].[ACCGR])*0.2742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-10),[ACCOUNT].[ACCGR])*0.1742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-11),[ACCOUNT].[ACCGR])*0.0833333

)*0.1731,

AVG(PERIODSTODATE([TIME].[LEVEL00],[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR])*0.014425)

but I still have a delta of 0,11

[former_member186338 · Accepted solution]

Sorry, but I see in your formula:

For Total: ...*0.1731...

For Month: *0.014425

????

[ChristianSass · Accepted solution]

0.1731 / year

0.014425 / month (0.1731/12)

[former_member186338 · Accepted solution]

Has to be the same! If you want to sum...

[former_member186338 · Accepted solution]

Look on the sample with the same coefficient for month and year with Excel calculation test:

Small rounding error in the 7 digit can be ignored!

Formula:

IIF([TIME].CURRENTMEMBER.PROPERTIES("PERIOD")="TOTAL",

(

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR])*3.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-1),[ACCOUNT].[ACCGR])*2.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-2),[ACCOUNT].[ACCGR])*1.6032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-3),[ACCOUNT].[ACCGR])*1.2698773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-4),[ACCOUNT].[ACCGR])*1.0198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-5),[ACCOUNT].[ACCGR])*0.8198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-6),[ACCOUNT].[ACCGR])*0.6532107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-7),[ACCOUNT].[ACCGR])*0.5103535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-8),[ACCOUNT].[ACCGR])*0.3853535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-9),[ACCOUNT].[ACCGR])*0.2742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-10),[ACCOUNT].[ACCGR])*0.1742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-11),[ACCOUNT].[ACCGR])*0.0833333

)*0.1731,

AVG(PERIODSTODATE([TIME].[LEVEL00],[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR])*0.1731)

[former_member186338 · Accepted solution]

Same for 1,2,3,...,12 data:

[ChristianSass · Accepted solution]

the AVG-formula / period is exactly what I need. We calculate an expense on an asset account per periode. At the end of the year I need the sum of P1 to P12

[former_member186338 · Accepted solution]

"We calculate an expense on an asset account per periode." - not clear! Why average???

But the formula work 100% correct. Tested on the real system data

[ChristianSass · Accepted solution]

This time I'm on vacation and the office is bothering me with this issue

I would like to com back on post: Aug 11, 2016 11:08 AM

Even if it is slow it should give me the right result by repeating the calculations on TOTAL level and sum that up. But there is an error in that since I've a dump in Excel

[former_member186338 · Accepted solution]

Sorry, you are doing something incorrect, may be typing mistakes or...

Please reproduce exactly my code!

I will never support "repeating the calculations on TOTAL level and sum that up" - incorrect idea!

[former_member186338 · Accepted solution]

Sample with coefficient 0.014425 for both month and year - also correct:

Compare with your picture

[ChristianSass · Accepted solution]

you are right! like always. Have a nice Week-End

[ChristianSass · Accepted solution]

Hi, I've to come back on this formula ....

Any idea what could be wrong with this formula? If I select only one year -> perfect!

But as soon as I insert a 2nd year I'll get a wrong result for both elements. After expanding the year on periods the result of the year level is correct again.

[former_member186338 · Accepted solution]

Please provide the test data set!

[ChristianSass · Accepted solution]

Different values but the same problem with the result

this is how the current formula looks like:

IIF([TIME].CURRENTMEMBER.PROPERTIES("PERIOD")="TOTAL",

((OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER),[ACCOUNT].[ACCGR])*3.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-1),[ACCOUNT].[ACCGR])*2.1032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-2),[ACCOUNT].[ACCGR])*1.6032107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-3),[ACCOUNT].[ACCGR])*1.2698773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-4),[ACCOUNT].[ACCGR])*1.0198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-5),[ACCOUNT].[ACCGR])*0.8198773+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-6),[ACCOUNT].[ACCGR])*0.6532107+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-7),[ACCOUNT].[ACCGR])*0.5103535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-8),[ACCOUNT].[ACCGR])*0.3853535+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-9),[ACCOUNT].[ACCGR])*0.2742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-10),[ACCOUNT].[ACCGR])*0.1742424+

(OPENINGPERIOD([TIME].[LEVEL02],[TIME].CURRENTMEMBER).LAG(-11),[ACCOUNT].[ACCGR])*0.0833333)*0.10,

AVG(PERIODSTODATE([TIME].[LEVEL00],[TIME].CURRENTMEMBER),CoalesceEmpty([ACCOUNT].[ACCGR],0))*0.10)

[former_member186338 · Accepted solution]

When I am asking about test data I am asking about 2 years test data. And better in the form I can copy paste.

[former_member186338 · Accepted solution]

Tested with 2 years on the same report, no issues!

[former_member232600 · Accepted solution]

whar mean SOLVE_ORDER=10 in this formula?? how it work?

[former_member186338 · Accepted solution]

It defines calculation order of different dimension member formulas if required!

[former_member232600 · Accepted solution]

please tell me where I can get acquainted in detail with the writing MDX / Member Formulas nested Formulas. Everythingt what I founnd - very briefly, without examples.

[former_member186338 · Accepted solution]

MDX is a complex language and you have to read a lot of documents on Microsoft site: MDX Function Reference (MDX)

For the rest - just search forum...

[former_member232600 · Accepted solution]

Thank you so much

[former_member200327]

What HANA Revision are you using?

[former_member186338]

Hi Christian,

First of all please test some simple nested formula, like:

KF_ONE=[BPCN92]

KF_TWO=[KF_ONE]

Is it working?

Second - you first formula can be optimized (without IIF - BASEPERIOD is a number, not string like MONTHNUM)

Vadim

[ChristianSass]

Hi,

Easy nested formulas do work

What do you mean "(without IIF ...)"? Can I replace the IIF? Could you give me an example?

thanx

[former_member186338]

Something like:

AVG(LastPeriods([H_TIME].CURRENTMEMBER.PROPERTIES("BASE_PERIOD")

,[H_TIME].CURRENTMEMBER),[H_ACCOUNT].[BPCN92]) * 0.01

[H_TIME].CURRENTMEMBER.PROPERTIES("BASE_PERIOD") is numeric, not string!

[ChristianSass]

Hi Vadim,

Web UI accepts the formula but in Excel I get a massive error

[former_member186338]

Please full info about BPC and BW versions/SP

Screenshot of the administration of H_TIME dimension with visible BASE_PERIOD.

Vadim

[ChristianSass]

Hi,

CPMBPC / HANA BPC 810 SP 08

SAP BW 740 SP 13

[former_member186338]

HI Christian,

Unable to test myself, I am on the vacation, writing using iPad....Try also strtovalue mdx function using monthnum ....

Vadim

[former_member200327]

there was a bug in some of the earlier Rev of HANA related to nested formulas. That's why, if you want to get some help, you need to answer the question I asked you a month back.