You are not logged in.

Ratio calculation rule

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

laloune

Sage

  • "laloune" started this thread

Posts: 217

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

  • Send private message

1

Monday, April 12th 2010, 10:40am

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 ?

Thanks in advance!
laloune

"To understand recursion, one must understand recursion"

Cuitlahua

Professional

  • "Cuitlahua" is male

Posts: 32

Date of registration: Feb 27th 2009

Location: Hungary

  • Send private message

2

Monday, April 12th 2010, 11:23am

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.

laloune

Sage

  • "laloune" started this thread

Posts: 217

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

  • Send private message

3

Monday, April 12th 2010, 11:31am

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

"To understand recursion, one must understand recursion"

Michel

Sage

  • "Michel" is male

Posts: 154

Date of registration: Jun 1st 2007

Location: Netherlands

  • Send private message

4

Monday, April 12th 2010, 3:28pm

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

Cuitlahua

Professional

  • "Cuitlahua" is male

Posts: 32

Date of registration: Feb 27th 2009

Location: Hungary

  • Send private message

5

Monday, April 12th 2010, 3:56pm

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).

laloune

Sage

  • "laloune" started this thread

Posts: 217

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

  • Send private message

6

Monday, April 12th 2010, 4:37pm

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

"To understand recursion, one must understand recursion"

Michel

Sage

  • "Michel" is male

Posts: 154

Date of registration: Jun 1st 2007

Location: Netherlands

  • Send private message

7

Tuesday, April 13th 2010, 7:08am

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

laloune

Sage

  • "laloune" started this thread

Posts: 217

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

  • Send private message

8

Tuesday, April 13th 2010, 9:53am

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

"To understand recursion, one must understand recursion"

Michel

Sage

  • "Michel" is male

Posts: 154

Date of registration: Jun 1st 2007

Location: Netherlands

  • Send private message

9

Tuesday, April 13th 2010, 10:47am

Hi laloune,

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

Michel

Etan

Intermediate

  • "Etan" is male

Posts: 15

Date of registration: Apr 16th 2010

  • Send private message

10

Friday, April 23rd 2010, 5:16pm

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

laloune

Sage

  • "laloune" started this thread

Posts: 217

Date of registration: Dec 2nd 2009

Location: Berlin

Hobbies: Born to work :-)

  • Send private message

11

Tuesday, April 27th 2010, 12:15pm

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 :rolleyes:

Thanks to all of you for your kindness !

kind regards
laloune

"To understand recursion, one must understand recursion"
Rate this thread