Calculation between entities
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
Answers
-
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,
1 -
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
0 -
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 !
0 -
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
0 -
Helmut Heimann , there is a value for each member behind the bar yes.
0 -
And I want to calculate the percentile
0 -
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
0 -
Helmut Heimann, this is what I did before I started with rules.. but my result is this and I could cry a river
0 -
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
0 -
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
0 -
Thank you Mukesh!
Where can I find this file?
0 -
Hello Antoni Theodorou
The file is in the attachment section just below my comment.
Thank You.
Mukesh Rathod
0 -
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
2 -
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
0
