Formula MDX in Dimensions for Account

[Former Member]

Hi!!

We are trying to calculate the Average of the price, and to do so we have created the next FORMULA in Account dimension:

IIF([MEASURES].CURRENTMEMBER.NAME="PERIODIC",[ACCOUNT].[INGNETO]/[ACCOUNT].[TONNETAS],SUM(PERIODSTODATE(YEAR,[TIME].CURRENTMEMBER),[ACCOUNT].[INGNETO])/ SUM(PERIODSTODATE(YEAR,[TIME].CURRENTMEMBER),[ACCOUNT].[TONNETAS])),Solve_Order=150

It should work ok, but it does not, when processing the dimension we get error

and the problem is that we use different TIME dimension in each of the applications.

We have two applications that used dimension ACCOUNT.

Application1 uses dimension TIME.

Application2 uses dimension TIMED.

The word TIME is had coded there so it will not work for Application 2 that uses TIMED instead and it sends error.

I think We need some MDX code that asks something like:

IFF APPLICATION= Aplication1 then.......Sum/TIME...... ELS Sum/TIMED.

The problem is that 'I haven't found any code related to Applications... or Cube name..

Please Advise!!!

[Former Member · Accepted solution]

Sorin, is correct that if you specity a dimension that it must be included in all applications that the dimension is included. However, you may be in luck with this formula because I do not think you have to specify [TIME].Currentmember. The periodstodate function always use the time dimension from the cube in MDX. Also, if you specify a level in the first parameter and leave the second parameter blank it will automatically use [TIME].Currentmember. Therefore, I think the following formula is identical and should pass validation because the time dimension is not named.

IIF(MEASURES.CURRENTMEMBER.NAME="PERIODIC",ACCOUNT.INGNETO/ACCOUNT.TONNETAS,SUM(PERIODSTODATE(YEAR),ACCOUNT.INGNETO)/ SUM(PERIODSTODATE(YEAR),ACCOUNT.TONNETAS)),Solve_Order=150

However, please note your original formula and this one will always be YTD for any measure not PERIODIC (including QTD) beucase you specify periodstodate(YEAR).

[Former Member · Accepted solution]

Thanks Kyle!!! You are the best!!!

I tried and the formula you provide me worked and its calculating correctly!!

Now I will not have to add another account dimension for this purposes!!

Thanks all of you for your quick response!!!

[sorin_radulescu]

Your account dmension is a share dimension.

You have to use just dimensions into your formula which are shared for all the applications where account is share.

Otherwise you will receive always an error.

To solve yourproblem You have two options:

1. Use different account dimensions for each application (and you will not have that problem with share dimension)

2. Instead to use dimension formul you have to use defaul tlogic to store the values calculated into that account.

Regards

Sorin Radulescu