Hi All
9.4 SP4
MS Excel 2003
I have written a rule that works perfectly, it acts as a balancing item in our Balance Sheet cube. The general contex is
If the Absolute value of Assets are < than the Absolute value of Liabilities, place Assets Minus Liabilities in an Account that rolls up into Assets. However when Itry to write the feeder i get #N/A's in the cube.
Rule:['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary']=N:IF(
ABS(['Local Currency','Periodic','CG11210_b','A000010_IAS_CG','End of Month Balance','INTL - Primary'])<ABS(['Local Currency','Periodic','CG11210_b' ,'L000012_IAS_CG','End of Month Balance','INTL - Primary'])
,ABS(['Local Currency','Periodic','CG11210_b','A000010_IAS_CG','End of Month Balance','INTL - Primary'])-ABS(['Local Currency','Periodic','CG11210_b','L000012_IAS_CG','End of Month Balance','INTL - Primary'])
,0);
Can anyone provide assistance with the best way to go with feeding the above
Regards Jon
Rule feeder produces #N/A in the Cube
-
- Posts: 11
- Joined: Thu Jan 08, 2009 1:53 pm
- OLAP Product: Cognos TM1
- Version: 10.1
- Excel Version: 2010
- Location: Essex
- mattgoff
- MVP
- Posts: 516
- Joined: Fri May 16, 2008 1:37 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: O365
- Location: Florida, USA
Re: Rule feeder produces #N/A in the Cube
Could you have a infinite loop? e.g. Does A000001_IAS roll up into A000010_IAS_CG or L000012_IAS_CG?
Matt
Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Rule feeder produces #N/A in the Cube
It would be unusal for a feeder to trigger a #NA error, unless there was some nesting or rolling forward logic happening.
I've not thought hard about it but is the logic a bit broken? If assets are > than liabilities, you'll get the wrong balancing figure?
Also IMO self balancing balance sheets are the devils work. If the balance sheet does not balance then your data feed is wrong or double entry has not been preserved and you need to know that something is broken not hide it with a balancing amount.
All that said the feeder would be
['Local Currency','Periodic','CG11210_b','L000012_IAS_CG','End of Month Balance','INTL - Primary']=>['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary'];
['Local Currency','Periodic','CG11210_b','A000010_IAS_CG','End of Month Balance','INTL - Primary']=>['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary'];
HTH
I've not thought hard about it but is the logic a bit broken? If assets are > than liabilities, you'll get the wrong balancing figure?
Also IMO self balancing balance sheets are the devils work. If the balance sheet does not balance then your data feed is wrong or double entry has not been preserved and you need to know that something is broken not hide it with a balancing amount.
All that said the feeder would be
['Local Currency','Periodic','CG11210_b','L000012_IAS_CG','End of Month Balance','INTL - Primary']=>['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary'];
['Local Currency','Periodic','CG11210_b','A000010_IAS_CG','End of Month Balance','INTL - Primary']=>['Local Currency','Periodic','CG11210_b','A000001_IAS','INTL - Primary'];
HTH
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 11
- Joined: Thu Jan 08, 2009 1:53 pm
- OLAP Product: Cognos TM1
- Version: 10.1
- Excel Version: 2010
- Location: Essex
Re: Rule feeder produces #N/A in the Cube
Steve many thanks for the reply, totaly agree with you on the false balancing item however this will only operate at a department level with further rules placing the unbalanced figure at a total company level. Tried you feeder suggestion but still getting #N/A's.
Long shot but should the rollups for Assets and Liabilities not include the balancing account for the rules as to me this seems almost like a circular reference ? Just thinking aloud (hoping noone can hear me)!
Actually just noticed Matts post above just thinking the same thing thanks for your assistance guys
Regards Jon
Long shot but should the rollups for Assets and Liabilities not include the balancing account for the rules as to me this seems almost like a circular reference ? Just thinking aloud (hoping noone can hear me)!
Actually just noticed Matts post above just thinking the same thing thanks for your assistance guys
Regards Jon
-
- Community Contributor
- Posts: 306
- Joined: Mon May 12, 2008 8:11 am
- OLAP Product: TM1
- Version: TM1 11 and up
- Excel Version: Too many to count
Re: Rule feeder produces #N/A in the Cube
Try taking out the N: on the right hand side, and re-running the rule.
Regards
Regards
Paul
- mattgoff
- MVP
- Posts: 516
- Joined: Fri May 16, 2008 1:37 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: O365
- Location: Florida, USA
Re: Rule feeder produces #N/A in the Cube
You could do that, but you probably just want to re-write your rule to not pull from the Assets consolidation but instead sum up all subconsolidations to avoid including your balancing element. e.g.:jonchrista wrote:Long shot but should the rollups for Assets and Liabilities not include the balancing account for the rules as to me this seems almost like a circular reference ?
Code: Select all
-Balance Sheet
-Assets
-SubConsolA1
-SubConsolA2
-BalancingElement
-Liabilities
-SubConsolL1
-SubConsolL2
['BalancingElement'] = N: ['Liabilities'] - ( ['SubConsolA1'] + ['SubConsolA2'] );
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
-
- Posts: 11
- Joined: Thu Jan 08, 2009 1:53 pm
- OLAP Product: Cognos TM1
- Version: 10.1
- Excel Version: 2010
- Location: Essex
Re: Rule feeder produces #N/A in the Cube
Matt, Steve
Hi Guys
Created the new rollups without the balancing item in them and using Steve's feeder all works perfectly
Thanks very much have a virtual beer on me !
Regards jon
Hi Guys
Created the new rollups without the balancing item in them and using Steve's feeder all works perfectly
Thanks very much have a virtual beer on me !
Regards jon