Mar 03, 2016 at 09:39 AM

MDX / Member Formulas nested Formulas

256 Views

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