Calculation between entities

Previous Member

Hey !

 

For the first day of the week, its me again.

 

Lets say we have a cube with an entity called "position" it has like 30 members showing 

 

various numbers. Now I put them into a Dataview and everything is fine. One of those entity

 

members is the total output. What I need is, a column with the percentage amount of the total output, for every single

 

entity in this dataview. As far as I know, calculation between entities are made with rules. I did it as the picture shows

 

but it gives me 0 for every member. 

 

I would love to solve this problem with you guys because I think it normally should be an easy thing

 

Thanks for your help again and have a nice monday ! 

 

Regrets 

 

Antoni Theodorou 

 

Unknown

Hi Antoni Theodorou,

    Your understanding is correct, but I wonder if the total line is actually zero. Since these are all members in the same entity, the total line is usually defined as a sum of other elements. If the total line is zero, then your rules won't work as designed. If this is the case, there are a few options.

• Populate the total line with the correct total during your load process
• Use something form the Nexel Complete Formulae library to make a calculation

 

    I'm sure there are other ways to approach it, but these are just two that come to mind.

Good luck,

Helmut Heimann

Hi Antoni Theodorou,

 

in addition to what Bob Gill already said, it would be helpful to have a screenshot of your dataview with and without the rule applied.

That would make it far easier to help you along.

 

Kind Regards,

Helmut

Previous Member

 

So here are my screenshots sorry for this black bar im not allowed to show any numbers..

the second column "%anteil an der Gesamtleistung 2011" should show the percentage amount of "Gesamtleistung= total output" for every member 

but as you can see, with the rule as shown apllied it shows me 0 for everything..without a rule applied its just the same cube as I use for the first column "aktuell 2011"

 

Im pretty sure I missed something or did something wrong cause this should be easy

 

 

 

Bob Gill I do not really understand what you mean by that

• Populate the total line with the correct total during your load process

Bob Gill and Helmut Heimann thanks a lot for your patience !

Helmut Heimann

OK, do you have figures on all of the entity members (the column behind the black bar) and just want to calculate the percentile? Or, do you have figures for just a few members? That would be good to know in order to see if your rules are set up correctly.

 

Kind regards,

Helmut

Previous Member

Helmut Heimann , there is a value for each member behind the bar yes.

Previous Member

And I want to calculate the percentile 

Helmut Heimann

Hi Antoni Theodorou,

 

then, I suppose you don't need rules at all.

Put the cube into the layout a second time, set "refer to" to "Gesamtleistung", hide this block and calculate the percentage in an algorithm, that's it.

For more information look here: References tab 

 

Kind Regards,

Helmut

Previous Member

  Helmut Heimann, this is what I did before I started with rules.. but my result is this and I could cry a river

Helmut Heimann

Hi Antoni Theodorou,

 

if you build up your dataview according to this (taken from the help page):

with all columns visible (it should look similar)--do you see figures in each cell?

 

Kind Regards,

Helmut

Previous Member

Hi Antoni Theodorou,

 

If you want to show the percentage with the help of entity calculation then you have to follow some step to achieve this.

please find the attached file for more details.

let me know if this fulfils your requirement.

 

Thank you.

Mukesh Rathod

 

   

 

Previous Member

Thank you Mukesh! 

Where can I find this file?

Previous Member

Hello Antoni Theodorou

 

The file is in the attachment section just below my comment.

 

Thank You.

Mukesh Rathod

Susanne Rossow

Hi Antoni,

to me it looks like your rule is not correct.

The "Gesamtleistung" normally is a calculated value and i presume that on your original cube "actuals" you have values on "Umsatzerlöse, Eigenleistung...ect" and Gesamtleistung is maybe calculated with another rule? Or do you really have values on "Geamtleistung" written in the actuals cube?

 

Regards

Susanne

Federico Favarin

Hi Antoni,

 

Basically you cannot set an algorithm that includes the same member that you have in the output line, beacuse, unless, you will have a circular reference and your value is, by defaul, set as "0";

 

You can simply use the function "RULE ." as explained in the handbook at chapter 6 / Rules / Define a rule:

 

" The column offset. This operator allows you to define a formula that refers to a previous column on the Layout. Normally, Rule formulas are calculations between cells located in the same column. The block offset allows you to refer to any cell on a previous column. A column offset is indicated by a dot (character ”.”) prior to the member code. For example, the formula =[.C01] refers to the cell on row C01 and one column to the left. This function avoids recursive resolution of formulas which refer to other calculated cells."

 

Hopefully this can solve your issues;

 

BR

 

Federico