# Ratio calculation rule

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Ratio calculation rule

Hello everybody,

I have a question concerning a ratio calculation rule:

I have an 'account' dimension with the following items:

Total inbound stock - total
=> inbound stock item 1
=> inbound stock item 2
=> inbound stock item 3

Total outbound stock - total
=> outbound stock item 1
=> outbound stock item 2
=> outbound stock item 3

My rule calculates the following:

Total ratio = total outbound / total inbound
=> ratio stock item 1 = outbound item 1 / inbound item 1
=> ratio stock item 2 = outbound item 2 / inbound item 2
=> ratio stock item 3 = outbound item 3 / inbound item 3

it does work for the 3 underelements, but not for the total element

any ideas ?

laloune

Post hoc, non est propter hoc
• # RE: Ratio calculation rule

Hello!

Some things to check:

Do you use N: / C: prefixes? If so, is n: used when the target area is base element and c: when consolidated element?
Do you have any other c: rules or rules without prefixes that may influence these rules?
Do you use markers?

C.
• Hi,

thanks for your response

actually yes, I duplicated the rule using, for one, the N: (that should treat the base elements), and the C: prefix for the second one (should treat consolidated elements)

it does work for N: elements, not for the C: one

I do not use markers (actually dunno what it is for :-S )

Thanks for your kind help
laloune

Post hoc, non est propter hoc
• Hi Laloune,

The N: and C: qualifiers specify that you want to restrict your rule to either leaf level ('N:') or consolidated level ('C:').

What you want is a rule that calculates at all levels (either leaf or consolidated). Leaving out the N: qualifier from the rule should do the trick for you.

Michel
• Hm.

Michel is completely right and you really should not write so many rules, one piece is enough. However, if everything else is correct, the duplicated rules should work as well.

If you still see problems then you may try looking for other rules that may influence these particular rules. For example rules with the same target area. It is possible that those N: - C:-s are missing somewhere and some rules calculate on elements where they should not or there is an unwanted overlapping.

C: rules also can produce surprising results when higher consolidations are viewed instead of the element itself for which the rule is written (I mean consolidations along other dimensions, sums of sums).
• yep, all that is actually right.

unfortunately, even if I write 1 only rule, letting apart the consolidation param out (N: or C:), the calculation done by the rule does not work on the consolidated level.

In some cases it does not matter (for example in case of a sum), but in my example I try to calculate a ratio. And the ratio of the sum on consolidated level is obviously different from the sum of the ratios
laloune

Post hoc, non est propter hoc
• Hi laloune,

Are the components that the ratio calculation is based on also rule calculated - could your problem be in the order of the rules?
Maybe you can post a bit more detail on your cube structure and the exact rules?

Michel
• Hi Michel,

actually no, there are no other rules that calculate this data... this is the only one

I have the impression that it comes from the hierarchy because it tries to overwrite the roll-up calculation induced by the hierarchy calculation...
laloune

Post hoc, non est propter hoc
• Hi laloune,

Could you please post the exact element-names and the exact rules?

Michel
• Hi all,

I don't know if the question is fully answerd. Please find attached my solution.

In my opinion you only must not consolidate the ratio-elements. Then you can use the 4 rules to calculate without N: or C: prefixes.

Regards

Etan
• Dear all,

sorry for my late response.

It works fine with Etan's solution, i.e. duplicating each rule for each level of the hierarchy to calculate the ratio on base level as well as on conso level.

Actually I thought that the problem (as well as the solution thus) was more complicated. It was not. I guess that my test phase slipped a little

Thanks to all of you for your kindness !

kind regards
laloune

Post hoc, non est propter hoc