Dynamically Read time period in MDX for member formula

[Former Member]

Experts,

I have a requirement in member formula where I need to read a value of account corresponding to December month. As the member formula will run for all the time periods, I need to read December month for every year.

For e.g

For January month of 2015

ACC1 =ACC2,[TIME].[2015.JAN]/ACC3,[2015.DEC]

For January month of 2016

ACC1 =ACC2,[TIME].[2016.JAN]/ACC3,[2016.DEC]

I am not specifying the time member in numerator as it has to run for all time periods as below but I am not able to derive the Dec month of current year dynamically.

ACC1 =ACC2/ACC3,[2015.DEC]

ACC1 =ACC2/ACC3,[2016.DEC]

I tried   this   ACC1=ACC2/ACC3,STRTOMEMBER("[TIME].[" + [TIME].CURRENTMEMBER.PROPERTIES("YEAR") +".DEC"+ "]"     but its not working.

Any suggestions and help will be appreciated.

Regards

Nikhil

Message was edited by: Nikhil Anand

[Former Member]

Experts,

Any suggestion or help.

Regards

Nikhil

[former_member186338]

Hi Nikhil,

Can you explain the calculation logic of your dimension member formula instead of "I need to read a value of account corresponding to December month..."

And business logic...

Vadim

[Former Member]

Hi Vadim,

I am using member formula to calculate certain ratios. The ratio calculation uses No of Days in a month and Year as parameter. In Below formula 980000002 is an AST type account which stores no of Days stored across each time member. For e.g. 31 for 2015.JAN and 59 for Feb and so on. I cannot change the ACCTYPE of this account as this is used in other calculation also. I need the value 365 which is stored in 980000002 across DEC month of every year. This ratio calculation is done for every month across all years. So basically I need to read 980000002 for YYYY.DEC.

I think I can use YYYY.TOTAL also  as 980000002 is an AST account.

[TIME].CURRENTMEMBER.PARENT.PARENT will result in YYYY.TOTAL, I think so 🙂

980034001 =( 549000000/980000002 (2015.JAN)*980000002 (2015.DEC))/980034001*100

                    =(29,786,719.25/31*365)/464,442,212.00*100

                     =75.513

ACCOUNT	ACCOUNT DESCRIPTION	EXAMPLE
980000134	Average Weighted Number of Shares	464,442,212.00
549000000	Attributable   Income	29,786,719.25
980000002	Days in Year	31 and 365 for Jan and Dec
980034001	Basic Earnings Per   Share	75.513

Regards

Nikhil

[former_member186338]

Hi Nikhil,

Just some additional info:

Cube default storage type - Periodic???

549000000 - INC?

980000134 - AST?

Please - sample for FEB!

Vadim

[former_member186338]

P.S. And:

[TIME].CURRENTMEMBER.PARENT.PARENT - yes, for AST it will be DEC

Vadim

[Former Member]

Yes Vadim you are correct.

Cube default storage type - Periodic

549000000 - INC

980000134 - AST

Example for Feb.

980034001 =( 549000000/980000002 (2015.FEB)*980000002 (2015.DEC))/980034001*100

                    =(29,786,719.25/59*365)/464,442,212.00*100

                     =39.67

Regards

Nikhil