TM1 Forum

Hi All,

I am using TM1 version 9.1.2 and have AllowNandCrules (don't remember the exact syntax) in the config file. My problem is I'm trying to write a C and a N rule and then consolidated the two together.

['US Unallocated','Toll']=C:DB('P&L','actual','Total',!Co&cost,'Tolls',!amonth)*(DB('customer profitability',!amonth,'AAG US','actual','$US',!SHIPLET,'Loaded Miles (Disp)')\DB('customer profitability',!amonth,'AAG US','actual','$US','Customer Profitability','Loaded Miles (Disp)'));

['Toll'] = N:if(DB('Terminal Info','Exp Base',!Co&Cost)@='Hauled',DB('P&L','actual','Total',!Co&cost,'Tolls',!amonth)*['Pct of Hauled Loaded Miles'],DB('P&L','actual','Total',!Co&cost,'Tolls',!amonth)*['Pct of Dispatched Loaded Miles']));

The N level Co&Cost (company and cost center i.e. Terminal 1, Terminal 2) and the C US Unallocated calculation work fine. My problem is the consolidation of the the two does not work.

'US' is a consolidation of Terminal 1, Terminal 2, and US Unallocated (containing several leafs). The US consolidation ignores the amount in the Unallocated consolidation. I have to have the US Unallocated calculated at the consolidation level because the miles and $ are in different leafs so the N level formula brings back zeros.

Can anyone help with this?

I hope that makes sense.

Thanks,

Mark

You need to realise how the TM1 consolidation engine works - it generally only sums up the values on (non-empty or fed) N elements to come up with the consolidated value and ignores anything on the intermediate C levels, whether they are rule-calculated or not.

You can force the calculation engine to behave differently by using the ConsolidateChildren function to indicate how the consolidation should be performed across specific dimension hierarchies. This, however, is not without some negative impact on the performance. I think you should first look at the possibility of bringing this calculation to the N level somehow and if it turns out to be unfeasible, apply ConsolidateChildren.

BTW, are you using skipcheck and feeders?

Hi

qml is right, you would be better doing this all at N level.

I suggest you add a base level element like US Unallocated - Input below US Unallocated and perform the calculation on the N: level on that.

You will then need to modify your main rule to say that is the N: level element is a component of US Unallocated then the value should be 0, otherwise your second rule will calculate in the default way for the elements below US Unallocated. If there is more than one level below US Unallocated, you will need to use ELISANC rather than ELISCOMP

Regards

Paul Simon

Paul/qml,

Thank you for your replies. I had considered Consolidatechildren but I have read that it should be avoided if possible. I will try the calculation at the N level first. I wanted to make sure there wasn't an alternative I didn't know about.

Thanks again.