TM1 Forum

Hi,

i'm trying to create a rule, that will for some condition skip aggregation this cell on consolidated level.
example:

| condition | costs
total 50
1 Yes 50
2 No 50


IF (condition = yes) this cell will be calculated on c: level (total)
else skip item counting, but leave the value in the cell

Can someone help me with this?
Thanks.

Legemza

Legemza wrote: i'm trying to create a rule, that will for some condition skip aggregation this cell on consolidated level.
example:

| condition | costs
total 50
1 Yes 50
2 No 50

IF (condition = yes) this cell will be calculated on c: level (total)
else skip item counting, but leave the value in the cell

I think that the first thing that you need to decide is what you mean by leave the value in the cell. TM1 will naturally consolidate. Consolidated elements cannot hold values. There are only two ways that they can get values:

1) via summing, ie consolidation, of base level items
2) via a rule. However, I would not recommend overriding the value of counts and sums on consolidations, since this can be difficult for end users to understand (Though it is fine and usually essential to do it with ratios and averages)

I would recommend that you have a scheme like this

C
/ \
A B D

Your rule might then say

['D'] = N:
IF( condition = yes
,
['C']
,
STET
) ;

Where STET means allow entry

Regards


Paul Simon

PaulSimon wrote:Legemza

Legemza wrote: i'm trying to create a rule, that will for some condition skip aggregation this cell on consolidated level.
example:

| condition | costs
total 50
1 Yes 50
2 No 50

IF (condition = yes) this cell will be calculated on c: level (total)
else skip item counting, but leave the value in the cell

I think that the first thing that you need to decide is what you mean by leave the value in the cell. TM1 will naturally consolidate. Consolidated elements cannot hold values. There are only two ways that they can get values:

1) via summing, ie consolidation, of base level items
2) via a rule. However, I would not recommend overriding the value of counts and sums on consolidations, since this can be difficult for end users to understand (Though it is fine and usually essential to do it with ratios and averages)

I would recommend that you have a scheme like this

C
/ \
A B D

Your rule might then say

['D'] = N:
IF( condition = yes
,
['C']
,
STET
) ;

Where STET means allow entry

Regards


Paul Simon

leave the value in the cell - i mean, that this value will be displayed in the cell, but not in the summary on consolidated level.

You haven't said where you are getting your "summing condition" from.
- Is it an attribute dependent on only a single dimension in the cube or is it a cell value in the cube or another cube that is dependent on the intersection of several dimensions?
- Is the condition slowly changing or rapidly changing?
- Do users require instantaneous recalculation of subtotals if the condition changes or is a slight delay acceptable?

These are important questions as the most efficient way to deliver your functionality would be to have a either separate rollup of members only where the condition is met and to rebuild this periodically or on demand, or to have a "matrix" solution and include the "condition" as a separate dimension in the cube and reload data to the cube. Note that these approaches would not deliver an instantaneous or "real time" solution but calculation and performance wise it would be the superior option.

If you need an instantaneous solution then you need rules, which is the approach Paul has described. Note that this will require an additional measure and this measure will not display the value where the condition is not met, it will display zero.