TM1 Forum

Issue: Incorrect consolidation for certain KPI

TM1 version 9.5.2 - 64 bit

Hello,

In a project we are facing a situation as described below –
Issue:
For Certain KPIs roll up of data from Branches to respective RO/ZO/HO is not happening correctly.

Description:
The KPI hierarchy against which issue is faced is as follows –

Description Attribute (Type)
Aggregate Deposit X
CASA G
Current Acct G
Savings Acct G
Time Deposit G
Time Deposit Card Rate G
Time Deposit High Cost G
Certificate of Deposit M

The Branch dimension (With multiple ZO, RO & Branch) has a hierarchy as follows –

Central_Office
Zone1
RO1
Branch1
Branch2
Branch3
RO2
Branch4
Branch5
Zone2

In the cube, for Central Office the consolidation of Aggregate Deposit is wrong.

Version Budget1
Branch Central Office
Measures Level
Month June June
Correct Consolidation Incorrect Consolidation Difference
Aggregate Deposit 19,973,956.70 19,353,987.69 619,969.00
CASA 6,889,167.03 6,889,167.03 -
Current Acct 1,357,477.21 1,357,477.21 -
Savings Acct 5,531,689.82 5,531,689.82 -
Time Deposit 11,295,589.67 11,295,589.67 -
Card Rate Time Deposit 7,051,406.00 7,051,406.00 -
High Cost Time Deposit 4,244,183.67 4,244,183.67 -
COD 1,789,200.00 1,789,200.00 -

Rule:
['Apr', 'Level'] =
IF (ATTRS('BudgetKPI', !BudgetKPI, 'View') @= 'Q',
['Quarter1', 'Level'] /3,
IF(ATTRS('BudgetKPI', !BudgetKPI, 'View') @= 'G',
DB('Branch_Planning', 'Actual', DIMNM( 'Year', DIMIX( 'Year', !Year ) - 1 ), 'Mar', !Branch, !BudgetKPI, 'Level') +
['Quarter1', 'iGrowth'] / 3,
STET
)
);

['May', 'Level'] =
IF (ATTRS('BudgetKPI', !BudgetKPI, 'View') @= 'Q',
['Quarter1', 'Level'] *2/3,
IF(ATTRS('BudgetKPI', !BudgetKPI, 'View') @= 'G',
['Apr', 'Level'] + ['Quarter1', 'iGrowth'] / 3,
STET
)
);

And so on...

# For KPI with attribute ‘G’ where input is by Quarter and as Growth,

[April, Level] will calculate as the addition of
[Mar, Previous Year] AND [Quarter1, Growth] / 3

[May, Level] will calculate as the addition of
[April, Level] AND [Quarter1, Growth] / 3

And so on....

# For KPI with attribute ‘Q’ where input is by Quarter and as Level,
[April, Level] will calculate as
[Quarter1, Level] / 3

[May, Level] will calculate as
[Quarter1, Level] * 2 / 3

And so on...

# Otherwise STET
Feeders:

['Quarter1', 'Level'] => ['Apr', 'Level'];
['Quarter1', 'iGrowth'] => ['Apr', 'Level'];
['Actual', 'Mar', 'Level'] =>
DB('Branch_Planning', !Version, DIMNM( 'Year', DIMIX( 'Year', !Year ) + 1 ), 'Apr', !Branch, !BudgetKPI, 'Level');

['Quarter1', 'Level'] => ['May', 'Level'];
['Apr', 'Level'] => ['May', 'Level'];
['Quarter1', 'iGrowth'] => ['May', 'Level'];

And so on...

Observation:

Prime facie the issue is noticed with KPIs where Budget Growth input is '0'.

If value of iGrowth for the Quarter is ‘0’, then consolidation is incorrect and where ‘Check Feeders’ shows correctly as ‘not fed’.

For last 3 months i.e. Jan, Feb and Mar the consolidation is correct. This may be because [Quarter4, iGrowth] has a rule which is Year – Q1 – Q2 – Q3 and so is never non-zero.

Why are the consolidated values not showing correctly?

There is underfeeding for the Branch and therefore the roll-up are not happening correctly. So how can we make the feeders work correctly for the above example?

Thanks in advance for your replies & possible solution.

Admittedly I've just skimmed your post but a few things I notice are:

* You don't specify N: in your rules so are potentially applying these calculations to the affected C level elements.
* You suggest that you've been checking feeders don't seem to mention whether you've found a cell that should be fed (is calculated and is non-zero) but isn't.

I would probably start by focusing on a single C level cell that is *wrong*. Use the rules tracer to see whether it is being consolidated or calculated (ie set by a rule). If it's calculated, look at your rule statement - ie do you really want to apply your rule to C elements in this region of the cube? If it's consolidated, identify which non zero leaf cells haven't been fed and look at your feeder statements to understand why.