TM1 Forum

Posted: **Tue Sep 10, 2013 11:18 am**

Hello everybody,

Please excuse my perhaps unintelligible subject. Im very new to working with a Cognos TM1 10.1 system (64Bit) for a small evaluation project.
I tried to find an answer myself by reading the Developers and the Rule Guide, but i didn't got it working.
I hope you can help me to solve my problem or give me a hint, where i can find a working solution.

My problem:
I created a cube that collects values from other cubes for further calculations based on rules. The cube has 4 columns (simplified): X Y A and B.
X and Y are the values that i need for calculating an average and for my later dashboard content. Column "A" stores the calculated average of the current line. All values belong to a region or maybe a subregion of a region. So some lines are consolidated values. in column "B" I want to store the average of the current consolidated line and continue writing that value for the corresponding leafs that belong to the current region.

For example:
X Y A B
1 1 1 1 (<- Region "Europe")
2 2 2 1 (<- Subregion "Germany")
3 3 3 1 (<- Subregion "Italy")
etc.

So I need a rule like ['B']=N:['Value of the last consolidated line'];

I want to draw some different diagramms with Excel and it makes it much easier for me, if I have all values that I need in a single line.
I hope you can help me.

With best reagrds,
strade

Posted: **Tue Sep 10, 2013 1:22 pm**

Here's what I think you're saying:

X and Y are values of leafs or consolidations in other cubes.
A is the average of X and Y, but since X and Y might be consolidations you also want:
B which is the average of all leafs which ultimately roll into X and Y

Is this correct?

Matt

Posted: **Tue Sep 10, 2013 1:29 pm**

This is somewhat tricky and I would't do this in a cube, I would do it in the report, assuming you are using Excel. Nevertheless, to do it in a cube you need a separate rule for N and C elements so you'll need to make sure you have the AllowSeparateNandCRules option set in your tm1s.cfg file. Your rule would look something like (this is not exact syntax since I don't know your cube structure):

Code: Select all

['B']=C:['A'],
      N:DB(CubeName,!Dim1,!Dim2,ELPAR('Region',!Region,1),'A');

Posted: **Tue Sep 10, 2013 2:00 pm**

Assuming that you have e.g. three other dimensions you could be consolidating over, and simple (i.e. single-parent) hierarchies on each, try something like this

Code: Select all

['B'] = IF( 0 < ELPARN( 'dim1', !dim1 ), DB( 'cube', ELPAR( 'dim1', !dim1, 1 ), !dim2, !dim3, 'B' ), CONTINUE );
['B'] = IF( 0 < ELPARN( 'dim2', !dim2 ), DB( 'cube', !dim1, ELPAR( 'dim2', !dim2, 1 ), !dim3, 'B' ), CONTINUE );
['B'] = IF( 0 < ELPARN( 'dim3', !dim3 ), DB( 'cube', !dim1, !dim2, ELPAR( 'dim3', !dim3, 1 ), 'B' ), CONTINUE );
['B'] = ['A'];

Top level on all the dimensions will get their values from A.
Top level on dim1 and dim2 will get their values from parents on dim3.
Top level on dim1 will get their values from their parents on dim2.
All other cells will get their values from the parents on dim1.

Is this what you were after?

Posted: **Tue Sep 10, 2013 2:13 pm**

Thank you for your answers!

I changed tomok's code so that it fitts for my application and it works PERFECT! Thanks a lot!
Thank your Duncan for your posting; I think I will need it for my further tryouts!

It already came up to my mind that the way I started is perhaps not best practice. But sometimes I need some positive results and a workaround my work too

Thanks again!
Thread can be closed...

TM1 Forum

Rule for continuously writing of a specific Value

Rule for continuously writing of a specific Value

Re: Rule for continuously writing of a specific Value

Re: Rule for continuously writing of a specific Value

Re: Rule for continuously writing of a specific Value

Re: Rule for continuously writing of a specific Value