TurboIntegrator Calculation Functions

[Sebastian Paulussen]

Hello all,

in the documentation for TM1 01.1 (Reference guide, Chapter 6. TM1 TurboIntegrator Functions pp. 205) you can find calculation functions for consolidation.

For 9.5.2 it can be found here.

The functions are marked as valid in TI and in rules.

After trying to use the ConsolidatedCountUnique and ConsolidatedAvg in my development environment and failing to get other results than #N/A, i did build a small 3 dimensional cube as state in the example to better understand the use of these functions.

My Rule

Code: Select all

['Average' ]  =  c: ConsolidatedAvg( 0,  'Income Statement', 'All Regions', '2010', 'Gross Sales' );

still returns a #N/A.

Rules Tracer throws a 'RULE LINE ERROR' and 'Calculated: -1.#QNB '.

Unfortunately my searches via google and n tm1forums didn't came up with any more than the function reference.

Did anyone successfully implemented the functions or can help me implementing the function in a rule?

[Wim Gielis]

Hello

Please try:

Code: Select all

['All Regions', '2010','Average' ]  =  c: ConsolidatedAvg( 0,  'Income Statement',!Region, !Year, 'Gross Sales' );

Change the Region and Year dimension names if needed to suit.

On the left hand side, you can take out the selections but I bet you will know this

[Sebastian Paulussen]

Hi Wim,

i tried the code with the whole dimension (!dimensionname) it resulted in the same error. The error seems to be independent which Area.

But, after rereading the Example i found my mistake.

Income statement in the example is a cube and a Dimension. The first Occurence in the example is referencing the cube. Since my usage is in a rule i can leve it blank resulting in:

Code: Select all

['Average' ]  =c:  ConsolidatedAvg( 0,  '',!Area, !Time2, 'Gross Sales' );

Without the restriction to consolidate elements i still get a #N/A for my leaf level Averages. I will post if get an answer to that.

Thanks a lot for your help.

[Wim Gielis]

My pleasure.

Without the restriction to consolidate elements i still get a #N/A for my leaf level Averages. I will post if get an answer to taht.

Do you mean that you have the same rule at N level too, so you want to show the average of 1 data point (Gross Sales) in the measure Average ?

[Sebastian Paulussen]

That is correct. I know it would not be of much use since the average of 1 Datapoint is the Value of the Datapoint itself. The purpose is here that i would not have to write another Rule. That could be handy in a Scenario where the Line of Business is allowed to change Rules via a Rules Workbook

[Wim Gielis]

I would just carry over the data at N level using a different rule on the same area.