Dimension member property in dimension formula

[Former Member]

I have to use time dimension member property in dimension formula but unable to use. None of the below syntax work and gives error. What am i missing.

I have to use act1/[time].currentmember.("MONTHNUM") but it is giving syntax error like invalid "/" in the formula and so on.

What is the correct syntax for dimension formula usage

Tried  below (BPC NW 7.5 SP11)

[ACCOUNT].[INV]/ ([TIME].CurrentMember.Properties("MONTHNUM"))   --  didn't work

[ACCOUNT].[INV]/strtovalue([TIME].currentmember.properties("MONTHNUM"))  -- didn't work

Any help would be appreciated.

-S

[former_member186338 · Accepted solution]

Hi Silpa,

As far as I remember, you can't use property value in the expression, because it's a text value. You can only have multiple IIF to compare this property with "1", "2",...,"12" and do the division by constant. Strtovalue is not supported.

B.R. Vadim

[Former Member · Accepted solution]

Vadim,

Thank you for your response.

We always have an option to store them as signed data and use the intersection in formula but i am skeptical from performance angle. Any alternate suggestion to use monthnum in dimension formula?

-S

[former_member186338 · Accepted solution]

Hi Silpa,

You can try to use something like COUNT(PERIODSTODATE.... Just test and compare report speed.

IIF may be not so bad:

IIF([TIME].CurrentMember.Properties("MONTHNUM")="1",[ACCOUNT].[INV],IIF([TIME].CurrentMember.Properties("MONTHNUM")="2",[ACCOUNT].[INV]/2,...

B.R. Vadim

[Former Member · Accepted solution]

Hi Vadim,

Similar case for me, can I use member formula as a sum of accounts with a particular property, l tried the syntax below but gets in error

Sum(Crossjoin({FILTER({[ACCOUNT].CURRENTMEMBER.PROPERTIES("DIMLIST_BDA"="SOURCE")}, {[AUDITTRAIL].[SAP]})))

Thanks

William

[pierodamora]

Hi Silpa,

try this...it works!

You must create a properties in Time dimension containing the month number ( i used "BASE_PERIOD" )

Then, in dimension formula, use this one:

(([ACCOUNT].[ID],[MEASURES].[YTD])/([TIME].CURRENTMEMBER.PROPERTIES("BASE_PERIOD")))

the result will be your account value ytd / month number.

Hope this can help!

P.

[former_member186338]

Hi Piero,

Please provide some details - BPC version, SP, HANA/Not HANA.

([TIME].CURRENTMEMBER.PROPERTIES("BASE_PERIOD")) - return string and normally you have to use StrToValue to convert it to numeric, But StrToValue is not supported in  BPC NW still.

B.R. Vadim

[Former Member]

Hi Vadim,

I'd like to do something similar, I need to use a property value in my dimension formula:

([CATEGORY].CURRENTMEMBER.PROPERTIES("CAT1")

Property CAT1 of my member returns ACTUAL.

I know this kinf of syntax works with IIF, but it does not help me ...

Any idea? I'm on BPC 10 NW (not Hana for my test but will be later)

Thanks for your help.

Regards,

Camille

[former_member186338]

Hi Camille,

Please provide FULL member formula, not just a part of it!

And add some explanation of what you want to achieve...

Vadim

[Former Member]

Hi Vadim,

I solve my previous issue with script logic, easier.

Now I have another question for you:

Can we, in BPC 10.0 NW, sum up all entities which have a particular property

Ex: I want to sum up all entities with property CURRENCY = USD

I was thinking of creating a member formula but can't get it work.

Thanks a lot for your help.

Regards

camille

[former_member186338]

No, you can't. You have to use hierarchy (may be additional) to get the sum of entities.

B.R. Vadim