Rule based on currently selected/active value of a Dimension

[kjanssen]

Hey there,

is it possible to write a rule that can work with the selected value of a certain dimension from the cube?

e.g.
we have a dimension of with some (consolidated) elements:

[*]Gross Turnover
[*]Net Revenue
[*]Gross Profit
[*]Contribution I
[*]...

each of these groups may countain 1-n sub groups or number elements.

and we have another dimension with our KPI currently only containing one element "Value".
(+ some more dimensions to complete our cube)


now we'd like to add another KPI "Value in % of Net Revenue" that should be calculated be a rule.

As I'm pretty new (first time actually working with) to rules I still have some troubles.

my first attempts where going another direction where i added a KPI Element for each of the groups mentined above and having a rule like this

Code: Select all

['Gross Profit in %'] = ['Gross Profit'] / ['Net Revenue'];

after the first "crash" this got extended by a feeder to get an acceptable performance

Code: Select all

SKIPCHECK;
['Gross Profit in %'] = ['Gross Profit'] / ['Net Revenue'];
# ...
FEEDERS;
['Net Revenue'] => ['Gross Profit in %'];
# ...

but this is not nice for two reasons:
1) I need a lot of rules to get a % element for all normal elements
2) It is not really flexible as I have to add a % elmenet + a new rule for each added "normal" element.


Now our Idea was to add the "Value in % of Net Revenue" KPI wit a formular like

['Value in % of Net Revenue'] = ['Whatever Element of the group is active right now'] / ['Net Revenue'];

But i have no idea if it is possible to have the red part as dynamic as "if the element 'xyz' is selected show the result of 'xyz / Net Revenue' as % and if it is 'abc' divide this value by Net Revenue".

I hope it is more or less clear what i want to achieve and someone has an idea to get near to this.


Thanks in advance

[Guillaume Galtier]

Now our Idea was to add the "Value in % of Net Revenue" KPI wit a formular like

['Value in % of Net Revenue'] = ['Whatever Element of the group is active right now'] / ['Net Revenue'];

But i have no idea if it is possible to have the red part as dynamic as "if the element 'xyz' is selected show the result of 'xyz / Net Revenue' as % and if it is 'abc' divide this value by Net Revenue".

Hi kjanssen,

you should try:
['Value in % of Net Revenue'] = N: ['Value'] / ['Net Revenue', 'Value'];

Hope this helps

[tomok]

Most good designs include a Measures dimension as the last one, even if you don't specifically need it. I would put an element called "Percent of Revenue" (this also assumes the raw data is in an element called "Values" in the same dimension) in the Measures dimensions. The rule would look like this:

Code: Select all

['Percent of Net Revenue'] = ['Value'] \ ['Net Revenue', 'Value'];

I don't know anything about the dimensionality in your cube so this may not be the exact syntax but you should get the idea.

[kjanssen]

Woah!

Thank you both for this incredibly fast response!


I'll try this approach... Looks like I was totaly by how the rules where before.

[kjanssen]

Just as a small feedback: This solution works like a charm.