TM1 Forum

We have a KPI that has to be weighted both by ownership & time -- both intra- & inter-month. In excel, it's relatively straightforward to calculate using sumproduct, but without that at our disposal we decided we'd better test the calc it in it's own cube. BUT...after getting it worked out, the code didn't translate into the 'production cube' (ie. the one where we'd like to have them reside) successfully. The denominator in the weighted average doesn't calculate and the c-level element is consolidated, per usual, instead of following the prescribed formula.

And based on other things we've read here (we're relatively new to TM1), it doesn't appear we can just pluck the consolidated numbers from the test cube, and plug them into the corresponding consolidated points in the production cube. So we're a bit stymied, and figured we'd turn here for advice.

Simplifying the element names, here's the code:

[flag] , [share] , [days operating] are all brought in from a different cube
All feed and consolidate as expected.

[weight] = N: [kpi 1] * [share] * [flag];
[numerator] = N: [kpi 2] * [weight] * [days operating];
These calc & consolidate as expected.

[denominator] = C: [weight] * [days operating] / [flag];
Works perfectly in the test cube, both for the company for each month & for all entities for YTD.
Returns zero in the production cube. (?!?)
(Note: Done at C-level, b/c we need SUM(X) * SUM(Y) / SUM(Z), not SUM( X * Y / Z ).)

[kpi 2] = C: [numerator] / [denominator];
Again, works perfectly in the test cube, both for the company for each month & all entities for YTD.
In the production cube, it just consolidates all the percentages instead of performing the calculation.
Even when we drop the C: from the denominator equation, to enable it to divide by a non-zero number (albeit a wrong one), it still does the regular consolidation.

Feeders, as follows:
[kpi 1]=>[weight];
[kpi 2]=>[numerator];
[weight]=>[denominator];
[numerator]=>[kpi 2];

You need to set the AllowSeparateNandCRules=T in the cfg file.

I know, it's stupid.

Martin

Found and changed the setting. Made no difference. Wasn't sold that it would, given that the test cube successfully handles the different N- & C- level formulae.

BTW, when you trace the calc, it indeed appears that it is looking to sum the subsidiaries as opposed to multiplying the 3 terms.

Hi Font,

Suggest you post the exact rules if you can.

It's probably an order (of the rule list) issue so look very closely at any difference between your test cube and the real deal.

Hi,

Rules at C: level often are a bit problematic, as they compete with the standard hierarchy consolidations. In a multidimensional cube there are several consolidation paths. TM1 automatically chooses the (perfomance) optimal consolidation path.
It could be that the consolidation path TM1 chooses is different between your two servers. You can force the required consolidation path using the ConsolidateChildren rules function.

Michel

fonthiller wrote:Found and changed the setting. Made no difference. Wasn't sold that it would, given that the test cube successfully handles the different N- & C- level formulae.

Did you restart the service after making the .cfg change? It only kicks in after this.

I had exactly the same problem once (moving a rule from dev to live and C rules stopped working) and it was because of this.

Are you copying your entire rule from the test server to the live server? If you're trying to surgically add lines then Steve's solution is more likely correct.

Martin

Thumbs up to Steve...

Moved the block of code from 2/3 the way down the file to the very top.

Interestingly, all the code that had been above it was written by QueBit. So it would seem that somewhere in there, their code nullifies the ability to have separate N- & C- level calcs.

Appreciate the responses!

fonthiller wrote: Interestingly, all the code that had been above it was written by QueBit. So it would seem that somewhere in there, their code nullifies the ability to have separate N- & C- level calcs.

The only way to properly nullify it is via that cfg parameter I mentioned. What's happening here is that rules work on a first come first served basis. The first rule that can be applied to a cell is applied. Any subsequent rules that would also be applied to that cell are ignored, that's why order of the rules is so important.

Presumably there's a rule that was written with an N or C qualifier, so applied to all types of cells. This rule was getting applied before the system got to your new rule.

Martin