TM1 Forum

Hi,
I need a rule that is after calculating the average at the consolidation level need assign that value to N level elements.

Say Group average=10 and also I want that 10 to beassigned to leaf level elements for group average.

['Group Average']=C:['Kgs']\['Metres']; say this is 10

['Group Average']=N:[ Value 10 should be assigned here]


regards,

abi

Abinaya wrote:Hi,
I need a rule that is after calculating the average at the consolidation level need assign that value to N level elements.

Say Group average=10 and also I want that 10 to beassigned to leaf level elements for group average.

['Group Average']=C:['Kgs']\['Metres']; say this is 10

['Group Average']=N:[ Value 10 should be assigned here]


regards,

abi

Hi Abi,

Can you please provide the structure of your consolidation and leaf level elements?
In both the statements you mentioned, same element. If that is the case then apply same calculation.

Regards,
Deepak Jain

Assuming your cube has 4 dims dim1, dim2, dim3, dim4 and the measures dim has elements: Kgs, metres, GroupAverage

And assuming you want to average on a consolidated element 'Total of dim3' on dim3, your rule would look like this.

your Clevels rules should be fine, and your N level rule would go like this:
['Group Average']=N: DB (<cubeName>, !dim1, !dim2, 'total of dim3' , !dim4, 'Kgs') \ DB (<cubeName>, !dim1, !dim2, 'total of dim3' , !dim4, 'Metres');

Now, you may choose not to go to the top-level element in the dim. you may chose to go just to the say the immediate parent of the element. This can be done by using ELPAR ('Dim3, !dim3, 1 ).

(Caveat: Please note, for ELPAR to work consistently with predictiable results, your dim shouldnt have multilpe hierarchies. Each element should have exactly 1 parent)

java_to_tm1 wrote:Assuming your cube has 4 dims dim1, dim2, dim3, dim4 and the measures dim has elements: Kgs, metres, GroupAverage

And assuming you want to average on a consolidated element 'Total of dim3' on dim3, your rule would look like this.

your Clevels rules should be fine, and your N level rule would go like this:
['Group Average']=N: DB (<cubeName>, !dim1, !dim2, 'total of dim3' , !dim4, 'Kgs') \ DB (<cubeName>, !dim1, !dim2, 'total of dim3' , !dim4, 'Metres');

Now, you may choose not to go to the top-level element in the dim. you may chose to go just to the say the immediate parent of the element. This can be done by using ELPAR ('Dim3, !dim3, 1 ).

(Caveat: Please note, for ELPAR to work consistently with predictiable results, your dim shouldnt have multilpe hierarchies. Each element should have exactly 1 parent)

Thanks for the reply. Group Average is done at the same cube so can I use the DB function? DB function is used only to derive value from source cube?

Yes, you can use the DB function to reference the cube that you are writing a rule for, it's not exclusive to referencing other cubes.

deepakjain2020 wrote:

Abinaya wrote:Hi,
I need a rule that is after calculating the average at the consolidation level need assign that value to N level elements.

Say Group average=10 and also I want that 10 to beassigned to leaf level elements for group average.

['Group Average']=C:['Kgs']\['Metres']; say this is 10

['Group Average']=N:[ Value 10 should be assigned here]


regards,

abi

Hi Abi,

Can you please provide the structure of your consolidation and leaf level elements?
In both the statements you mentioned, same element. If that is the case then apply same calculation.

Regards,
Deepak Jain

I have Dim1 ,Dim2 , Dim3, Dim4 and measures dimension.

Group Average at consolidation will calculate the sum of kgs\Sum of meters (C:['Kgs']\['Metres']). Once this is calculated I want to populate the leaf level with this value.

In another words If say Group average is 10 I want to populate leaf level elements in the Dim 4 with that value. If we have the facility to define to variable in TM1 rules then I could assign the value to that variable and then assign the variable to N level elements

['Group Average']=N:[ C:Group Average] this gives error but some thing like this I need. It's quite unusual way to assign consolidation value to leaf level value. But that is what I need.

jacktuckerman wrote:Yes, you can use the DB function to reference the cube that you are writing a rule for, it's not exclusive to referencing other cubes.

Thanks I will try this and come back.

Abinaya wrote:

jacktuckerman wrote:Yes, you can use the DB function to reference the cube that you are writing a rule for, it's not exclusive to referencing other cubes.

Thanks I will try this and come back.

Hi Jack,
The TM1 rules guide says you can use DB function on the same cube when you are refrencing the string element,As per your reply I infering it can be also be used for numeric elements also,Please confirm.Thanks

BariAbdul wrote:

Abinaya wrote:

jacktuckerman wrote:Yes, you can use the DB function to reference the cube that you are writing a rule for, it's not exclusive to referencing other cubes.

Thanks I will try this and come back.

Hi Jack,
The TM1 rules guide says you can use DB function on the same cube when you are refrencing the string element,As per your reply I infering it can be also be used for numeric elements also,Please confirm.Thanks

It can be used.

Regrads,
Deepak Jain

Not for the first time in a decade or so the documentation leads you up a blind alley, or, at least doesn't show you the whole route home.

A basic rule on a four dimension cube that states:

['Result']=N:['Element 1']*['Element 2'];

Can also be written as:

['Result']=N:DB('cubename',!dim1,!dim2,!dim3,'Element 1')*DB('cubename',!dim1,!dim2,!dim3,'Element 2');

And produce exactly the same result.

Jack

Thanks Jack,But if yeilds same result then what sort of scenerios we need to use DB refrence on the same cube.Does it makes any difference in performance.

I'd use it in any scenario where you call on dimension based logic such as the ELPAR suggestion mentioned before.

Jack

Thanks a lot Jack.

BariAbdul wrote:Thanks Jack,But if yeilds same result then what sort of scenerios we need to use DB refrence on the same cube.Does it makes any difference in performance.

Performance is measurably worse when using DB references rather than the shorthand bracket notation. That is why it's best not to use the DB notation if you don't have to. You do have to use it if sourcing from another cube, doing any string calculations, or nesting any other rule functions in the reference.

Shorthand notation on the right-hand side of a rule statement is only appropriate where the source of formula is all self-contained in the same cube and you are not modifying element names. If you are either 1) using attributes for looking up values, 2) looking to another cube for values or 3) messing with element names like concatenating strings, using SUBST, TRIM, etc., then you have to use the DB reference. If the shorthand notation is not appropriate for what you are tring then you'll get a syntax error most of the time.