Rules and consolidation

[telula]

All,
I have this hierarchy

Total Depreciation Amount
- Calculated Depreciation
- Actual Depreciation
- Manual Adjustment

where Calculated Depreciation and Actual Depreciation have rules but Manual Adjustment doesnt.
I have these feeders
['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];
['Actual Depreciation Amount']=>['Total Depreciation Amount'];
['Manual Adjustment']=>['Total Depreciation Amount'];

It seems that the Total Depreciation Amount does not show the consolidated value unless there is an amount in Manual Adjustment. Can anyone tell me where I have gone wrong?

Thanks

[kpk]

The
- Calculated Depreciation
- Actual Depreciation
elements must be fed also.
You can check them with the Rule Tracer.

Regards,

Peter

[Gregor Koch]

Hi

- Calculated Depreciation
Please post the rule which calculates this measure as it will be the one you actually need to feed

- Actual Depreciation
In case this is a loaded value you don't need to feed this measure. Otherwise post the rule which calculates this measrure.

- Manual Adjustment
In case this is a manual entry, you don't need to feed this measure

Cheers

[telula]

Gregor,

['Forecast','Calculated Depreciation']=N:IF (!GL Account @='A321551', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2416211','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321551'),
IF(!GL Account @='A321531',DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351011','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321531'),
IF(!GL Account @='A321532', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321532'), IF(!GL Account @='A321533', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351013','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321533'), IF(!GL Account @='A321534' , DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351014','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321534'), IF(!GL Account @='A321512' , DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2321012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321512'),IF(!GL Account @='A321522' ,DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2341012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321522'),0)))))));

['Actual','Actual Depreciation Amount']=N:IF(ELISANC('GL ACCOUNT','A321500',!GL Account)=1,DB('Expense','Actual',!GL Account,!Projects By Division,'All Contracts',!Year,!Month,'All Rows','Amount'),0);

[Gregor Koch]

Hi

Should have said this before but to write the Feeder for ['Forecast','Calculated Depreciation'] I would need to know the dimensionality of the cube it resides in (is it 'DepreciationCalc' ?). Generally speaking your Feeder could be based either (and this is simplified as a combination is valid too) on 'Cost' in the 'DepreciationCalc' cube or 'A321551' in your 'Assumption' cube. I'd opt for 'Cost' because it seems logical that if you don't have (accumulated) cost you don't have anything to depreciate.

For ['Actual','Actual Depreciation Amount'] something like this in your 'Expense' cube should do the trick (ignoring the ELISANC condition for starters)

['All Contracts', 'All rows', 'Amount']=>DB('your depreciation cube', dim1, dim2, ..., 'Actual Depreciation Amount');

Hope this helps.

[telula]

Sorry, I didnt show everything in the earlier post but these are the complete feeders

Feeders;
[{'Actual','Forecast'},'Cost']=>['Actual+Forecast','Cost'];
[{'Actual','Forecast','Actual+Forecast'},'Cost']=>['Calculated Depreciation'];
['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];

['Actual','Actual Depreciation Amount']=>['Forecast','Actual Depreciation Amount'];

[{'Actual','Forecast'},'Actual Depreciation Amount']=>['Actual+Forecast','Actual Depreciation Amount'];


['Forecast','Actual Depreciation Amount']=>DB('DepreciationCalc','Forecast',!Year,DIMNM('Month',DIMIX('Month',!Month)+1),!Projects By Division,!GL Account,'Actual Depreciation Amount');

['Actual Depreciation Amount']=>['Total Depreciation Amount'];

['Manual Adjustment']=>['Total Depreciation Amount'];

['Forecast','Total Depreciation Amount']=>DB('Expense','Forecast',!GL Account,!Projects By Division,'Unspec Contract',!Year,!Month,'Row 1','Amount');

and these are the feeders from the expense cube

['Forecast','All Contracts',{'2321012','2341011','2351011','2351012','2351013','2351014','2416211'},'All Rows','Total Amount']=>DB('DepreciationCalc','Forecast',!Year,!Month,!Projects By Division,!GL Account,'Cost');

['Actual','All Contracts',{'A321512','A321521','A321522','A321523','A321531','A321532','A321533','A321534','A321535','A321551','A321553'},'All Rows','Total Amount']=>DB('DepreciationCalc','Actual',!Year,!Month,!Projects By Division,!GL Account,'Actual Depreciation Amount');

[Gregor Koch]

Well,

That's a whole lotta code I would have to read through to get me somewhere I could help you more.
Are those Feeders the ones you have written after your read my post or before?

It would make it a lot easier if we could focus on one rule one feeder (which you want to get to work) for starters.
Again, for this it also helps to know where (cube) what (measure) is and the dimensionality of that 'where'.

I suggest you start with a clean slate by resaving your rules without the Feeders in question and unloading and reloading the involved cubes.
If you have been playing around with those Feeders for a while you might still have some 'flags' sitting which are not set by your current Feeder statements.

Maybe also go to
http://wiki.olapforums.com/index.php?title=TM1_Feeders
and
http://forums.olapforums.com/viewtopic. ... 529&p=8198

Cheers

[garry cook]

Well, it's getting late but I'm bored (TI processing around 16 millions lines of data in background) so going to have a stab at this.

First problem appears to be a simple enough cause -

I have this hierarchy

Total Depreciation Amount
- Calculated Depreciation
- Actual Depreciation
- Manual Adjustment

where Calculated Depreciation and Actual Depreciation have rules but Manual Adjustment doesnt.
I have these feeders
['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];
['Actual Depreciation Amount']=>['Total Depreciation Amount'];
['Manual Adjustment']=>['Total Depreciation Amount'];

It seems that the Total Depreciation Amount does not show the consolidated value unless there is an amount in Manual Adjustment. Can anyone tell me where I have gone wrong?

That's because you're feeding into Total Depreciation Amount which is a consol. Feeding a consol is shorthand for feeding the children of the consol. Therefore

['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];

reads to the system as

"If there's a number in Forecast/Calculated Depreciation, feed Forecast/Calculated Depreciation and Forecast/Actual Depreciation Amount and Forecast/Manual Adjustment"

That's why when you put in a manual Adj (the only place you can actually load something as the other two are rule calc'd) it works because the manual entry is pretty much feeding the consol. Easiest way to think about it is that essentially a consol is a system based rule that adds up the nodals beneath it. Therefore an entry in the nodal points of a consol act as a system feeder to make the system realise there's a number that needs to be displayed. No doubt someone can explain that better than I can at the minute but that's the easiest way to think about it.

Causes probs sometimes when you calc a C: level rule that doesn't have any data below it. Sounds stupid but it does happen. Have had to create an extra element that calc's at 1x10E-59 before to get the C: level to feed. It's all because you can't feed a Consol as the feeder will read it as "feed the children of the consol". Can be expanded to really say "feed all my bottom level nodals" rather than children to avoid confusion so you can't just add another level of consol to trick the system, you need a bottom level nodal entry at some point to force a display of the consol. Hope that bit makes sense.

A side effect in your example by the way is also that you're essentially feeding Calculated Depreciation and Actual Depreciation from Manual Adj. Therefore, my gut feeling is that you're overfeeding your model with these feeders as Manual Adj is highly unlikely to be the driver for the other two calcs. Feeding consolidation is a concept that gets a lot of people.

Now that's out the way, I assume that neither of the feeders for Actual or Calculated Dep are working (otherwise these should have acted as data that would have caused the Total to display).

['Forecast','Calculated Depreciation']=N:IF (!GL Account @='A321551', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2416211','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321551'),
IF(!GL Account @='A321531',DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351011','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321531'),
IF(!GL Account @='A321532', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321532'), IF(!GL Account @='A321533', DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351013','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321533'), IF(!GL Account @='A321534' , DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2351014','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321534'), IF(!GL Account @='A321512' , DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2321012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321512'),IF(!GL Account @='A321522' ,DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,'2341012','Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown','A321522'),0)))))));

Feeders;
[{'Actual','Forecast'},'Cost']=>['Actual+Forecast','Cost'];
[{'Actual','Forecast','Actual+Forecast'},'Cost']=>['Calculated Depreciation'];
['Forecast','Calculated Depreciation']=>['Forecast','Total Depreciation Amount'];

['Actual','Actual Depreciation Amount']=>['Forecast','Actual Depreciation Amount'];

[{'Actual','Forecast'},'Actual Depreciation Amount']=>['Actual+Forecast','Actual Depreciation Amount'];


['Forecast','Actual Depreciation Amount']=>DB('DepreciationCalc','Forecast',!Year,DIMNM('Month',DIMIX('Month',!Month)+1),!Projects By Division,!GL Account,'Actual Depreciation Amount');

['Actual Depreciation Amount']=>['Total Depreciation Amount'];

['Manual Adjustment']=>['Total Depreciation Amount'];

['Forecast','Total Depreciation Amount']=>DB('Expense','Forecast',!GL Account,!Projects By Division,'Unspec Contract',!Year,!Month,'Row 1','Amount');

couple of general points here -

is there a reason that you have rule driven Actual+Forecast? A consolidation would be a hell of a lot faster and make your rule/feeder writing a lot easier.

your calc'd dep rule is pretty messy considering it's doing the same thing again and again with different codes. Would put the codes you want to fire from (A321551, A321531, A321532, A321533, A321534, A321512, A321522) into a consolidation - I'm going to call it Zanzibar for the following example. Then create a mapping cube with these elements in a dim and a string field with one element. Then map in the codes. Then rewrite your rule as

['Forecast','Calculated Depreciation']=N:IF
(ELISANC('GL Account','Zanzibar',!GL Account)=1,
DB('DepreciationCalc','Actual+Forecast',!Year,!Month|' YTD',!Projects By Division,DB(REFCUBE TO YOUR MAPPING),'Cost')\
DB('Assumption','Actual',!Year,'All Months','Unknown','unspec','Unknown',!GL Account)
,STET or CONTINUE depending on which you want);

then use a feeder from Depreciation Calc along the lines of

['Actual+Forecast','Cost','{2416211,2351011,2351012,2351013,2351013}']=>DB('DepreciationCalc','Forecast',,!Year,SUBST(!Month,LONG(!Month)-4),!Projcts By Division,'Zanzibar','Calculated Depreciation');

as this seems to be where your data is driven from. Could always create a consol with these elements also to trim your rule further or specifically point them at the the appropriate areas within Zanzibar if you want to be as lean as possible if you so desire.

['Actual','Actual Depreciation Amount']=N:IF(ELISANC('GL ACCOUNT','A321500',!GL Account)=1,DB('Expense','Actual',!GL Account,!Projects By Division,'All Contracts',!Year,!Month,'All Rows','Amount'),0);

['Actual','All Contracts',{'A321512','A321521','A321522','A321523','A321531','A321532','A321533','A321534','A321535','A321551','A321553'},'All Rows','Amount']=>DB('DepreciationCalc','Actual',!Year,!Month,!Projects By Division,!GL Account,'Actual Depreciation Amount');

Think the bold !GL Account is what's causing you a problem potentially although very hard to tell without looking at it in more detail. I'm guessing that changing that to 'A321500' will force a feed down that consol which hopefully should sort out your problem. Or maybe it's Zanzibar thinking about it. Getting too late and far on in this post to be bothered to go back and try and repick - sorry

Once you get the feeders for Actual and Forecast Dep Amount sorted correctly, it'll flow up to your consol to sort your initial problem.


Also, as an aside, noticed you have

DIMNM('Month',DIMIX('Month',!Month)+1),!

somewhere in your rules. That's 'orrible - much safer to change that to an attribute lookup against month for next period in the rule and previous period in the feeder. Saves someone killing your rules by adding a rogue element in the month dim.

Apologies if the syntax went a bit screwy towards the end, KMFDM came on my IPod and that always ruins my concentration

Anyroad, give that a bash, hope it helps. My TI has just finished loading so going to go and try to unpick my own code now