Ratio calculation

[Wim Gielis]

Hi all,

Just a random question - most probably there will be no right or wrong answer
I am writing rules for ratios, like the following (margin calculation of turnover minus costs for example):

['MyRatio'] = ( ['A']-['B'] ) \ ['A'];

but my initial thought was to write it as:

['MyRatio'] = 1 - ['B'] \ ['A'];

Both terms are DB functions towards a cube with a great number of dimensions and detail. Form 2 will be shorter for notation.
I want to show 0 whenever A is 0, so the 2nd form will need an additional IF to test if A = 0 or not. Form 1 takes care of this as such.

What would you prefer if any? Just curious to see what you think of it.
Would there be a speed difference in the formulas as they are above, maybe because ['A'] is retrieved only once in formula 2? Or is it cached and the speed difference is minimal?

Have a great day,

Wim

[jim wood]

There are so many other factors that could possibly have an impact on this decision so I guess the answer would be it depends.....

[Martin Ryan]

Give your requirement that if A is zero then the answer should be zero, I'd go with the first option. I think (but have never tested) an if statement is likely to be slower than a second retrieval. And in fact if you have to add the if statement, then you're adding in the second retrieval and the if statement. So there's more going on.

Also, to my eye, the first one is easier to comprehend as well. The second is less obvious, particularly when you obfuscate it with an if statement to boot.

Martin

[jstrygner]

Additionally if we consider speed, in the first one you can build difference between A and B as a consolidation with weights 1 and -1.

[Wim Gielis]

Additionally if we consider speed, in the first one you can build difference between A and B as a consolidation with weights 1 and -1.

In this particular case, A and B are DB() formulas towards a second cube.
A and B would be elements in the dimension in your case (which is a good idea too).

But as we can conclude by now, there is much more going on than only the structure of the formula.

Another one of the ratio's, was:

' A
_________________

' A + B * C

I would love to implement it as: 1/ (1 + B*C/A)
... provided I add a comment in the rules or a note in the documentation on the model.
But as said, there is an important trade-off: easier formulas, readability, speed difference (if any).

Thanks for your input in this topic. Good night.