0

# Formula on MEMBER Question

Jan 30, 2017 at 05:30 PM

37

Former Member

Hello Experts,

I have a dumb question. Is it possible on member fórmulas to include different Dimension?

I Ask because I have the next Formulas on member in dimension ZSTAGL, (ZSTAGL is User Defined Dimension):

The only formula that seems to be working is for OCCUPANCY since it makes reference to the same Dimesion ZSTAGL, the other 2 fórmulas that make reference to PF_CUENTAS (ACCOUNTS) and combine ZSTAGL are not working.

I am assuming that the formula is incorrect.

Am I right or is it possible to combine dimensions and I am missing something?

Thanks for the posts.

Regards.

Vadim Kalinin Jan 30, 2017 at 05:39 PM
1

It's possible but only if you have same dimensions (used in formula) for all models in the environment!

If you have the model where you have not all dimensions you will be unable to save formula.

Show 8 Share
Former Member

So it means that the formula I have for ADR and REV-PAR will never work the way I have them??

Former Member

I don't know :) You have provided no information about models and dimensions used in the environment!

Former Member

I have an envirnment with 5 models. I just want to make the calculation for one model. In this model, I have the next Dimension:

Entity (E), Currency (R), PERIODO(T) , ASSUMPTIONS(U), CEBE(U), CECO(U), PF_CUENTAS(A), SOCIEDAD(U), ZSTAGL(U), PF_CATEGORY(C).

The dimensions that I have the option checked for formulas on member are: PF_CUENTAS and ZSTAGL.

I need to Calculate for Dimensions Members (all in ZSTAGL Dimension)

2. OCCUPANCY= [ZSTAGL].[H_HABI1] / [ZSTAGL].[H_OCUP] <------ this is the only formula working.

3. REV-PAR = [PF_CUENTAS].[LODGING] / [ZSTAGL].[H_HABI1]

I am wonder if formula 1 and 3 are allowed, because they are not making the calculation in the template as formula 2 is.

formulas.jpg (20.5 kB)
Former Member

"I have an envirnment with 5 models" - you have to provide dimension lists for ALL models!

Former Member

Sample:

If in another model you also have dimension ZSTAGL, but you don't have dimension PF_CUENTAS then this formula is invalid!

Former Member

Then I guess that is the problem, in the other models I have different Dimension for ACCOUNTS. So will the formula be invalid even for the model I want to have the calculation for? I just want the calculation for the specific model I mentioned.

Regards.

Former Member

Please provide dimension lists for all models!

member formula is not for single model it's for all models in the particular environment (where the dimension with member formula is used)!

Former Member

Sample:

You have ACCOUNT type dimensions (different in different models)

You have single TIME dimension used in all models.

You can create formulas in the ACCOUNT dimension using members of TIME dimension - no issue!

Vadim Kalinin Jan 30, 2017 at 07:28 PM
1

Ups, sorry! I see that you are able to validate dimension! It means the formulas are using allowed dimensions!

But the formula calculation logic is strange!

[PF_CUENTAS].[LODGING] / [ZSTAGL].[H_OCUP]

You have to use tuple like:

([PF_CUENTAS].[LODGING],[ZSTAGL].[XXXX]) / [ZSTAGL].[H_OCUP]

Show 9 Share
Former Member

Wow, now I saw want you meant by having the all the dimensions in all models. The formula was processed and accepted, i got warnings tho. The warning said that the dimension PF_CUENTAS does not exist in the other models. I tryied with the tupples as wrote above plus ([PF_CUENTAS].[LODGING],[ZSTAGL].[XXXX]) / ([PF_CUENTAS].[XXXX],[ZSTAGL].[H_OCUP]).

but didn't work, ill try to include all dimensions in the tupple expression

Former Member

"include all dimensions"? What for?

Just the ZSTAGL dimension member!

Former Member

Please explain the calculation logic you want to achieve!

Former Member

In [PF_CUENTAS].[INCOME], I have the income for Room Rental. In [ZSTAGL].[H_OCCUP] I have the quantity of Rooms that were rental. The calculation means my average Rate per room. I guess will be similar like a calc with a relation Account / Product. The combination is: when I have [ZSTAGL].[H_OCCUP], The account is NA, then I have Account, the ZSTAGL is NA. My formula is:

([PF_CUENTAS].[LODGING],[ZSTAGL].[NA_ES])/([PF_CUENTAS].[NA_ACC],[ZSTAGL].[H_OCUP])

I wen to the template, but no calculation was performed.

I think will be faster if I use excel formula.

Former Member

Sorry, still not clear! Can you show the report screenshot with members and figures of initial data?

[PF_CUENTAS].[INCOME] and formula:

([PF_CUENTAS].[LODGING],[ZSTAGL].[NA_ES])/([PF_CUENTAS].[NA_ACC],[ZSTAGL].[H_OCUP])

???

Former Member

Here is an example, using random data. But the point is the next: I want to calculate ADR and REV-PAR. ADR= G24/G18, REV-PAR is G24/G17 % OCCUP is calculated G18/G17. <- tHis is the only fórmula that actualy worked. In column "G" I don't have excel fórmulas just in "H" and "I" for ADR and REV-PAR. What you see in the image are 2 reports for G16 to G21 is "000" and for G22 to G25 is "001". All are Dimension members, not local members even they have different format.

"000" Displays ZSTAGLE and "001" Displays Accounts. I Guess will be better using excel fórmulas, right?

Regards.

example.jpg (94.8 kB)
Former Member

But what is the PF_CUENTAS for report 000

And what is the ZSTAGLE for report 001?

Former Member

By the way, why do you use separate dimension ZSTAGLE??

Why not to have this members in the ACCOUNT type dimension PF_CUENTAS??

Strange...

Former Member Jan 30, 2017 at 11:20 PM
0

Ohhh... It is because those are BW dimension that they use to do other reporting. And I was asked to respect that structure. Those are dimension without currency,

Show 1 Share

To my mind it's an incorrect design in general. Remove the ZSTAGLE dimension and add the members to

PF_CUENTAS.

P.S. Looks like a lot of customers add a lot of dimensions to the models and later unable to understand why BPC is so slow :)