Help with rule please...

[Christian]

Hi all,

I have a capex/depr expense cube where I use a picklist for the managers to select the asset type...I then use a cumulation function so I have that asset type value in all the subsequent months for reference.

I have a separate Depreciation Allocation cube where I need to bring in the total new depreciation expense by asset type. So...I need to reference the asset type in the asset type cumul field, where it is "A" or say "Office Equipment" in this example, then return the depreciation expense for that month for all capital purchases where "Office Equipment" is item purchased. Sounds simple...not for my feeble mind. I think it has something to do with 'Asset Type Cumul' being a String value.

This is what I tried...

['Total Office Equipment' ] = IF (DB('Capex', !Version, !Department, !Country, !Month, !Portfolio TBD, !Capital Exp, 'Asset Type Cumul') @= 'Office Equipment',

DB('Capex', !Version, !Department, !Country, !Month, !Portfolio TBD, !Capital Exp, 'Depreciation Expense on New Purchases'),
STET);

Please help!

Thanks,
Christian

[Michel Zijlema]

Hi Christian,

I think restricting the rule to leaf level by prefixing the formula with N: will do the trick.
I assume 'Asset Type Cumul' value is only set at the leaf level.

Michel

[Christian]

Correct...I'll give that a shot Michel. Thanks mon ami.

[Christian]

Hmm...it says invalid string expression. The rule now reads...


['Total Office Equipment' ] =N: IF (DB('Capex', !Version, !Department, !Country, !Month, !Portfolio TBD, !Capital Exp, 'Asset Type Cumul') @= 'Office Equipment',

DB('Capex', !Version, !Department, !Country, !Month, !Portfolio TBD, !Capital Exp, 'Depreciation Expense on New Purchases'),
STET);

[Christian]

I need to write a rule like this for about 10 asset types...the logic seems to make sense to me, I just can't get TM1 to agree.

[Michel Zijlema]

Hmm...it says invalid string expression. The rule now reads...


['Total Office Equipment' ] =N: IF (DB('Capex', !Version, !Department, !Country, !Month, !Portfolio TBD, !Capital Exp, 'Asset Type Cumul') @= 'Office Equipment',

DB('Capex', !Version, !Department, !Country, !Month, !Portfolio TBD, !Capital Exp, 'Depreciation Expense on New Purchases'),
STET);

Hi Christian,

Based on your initial post I understand that the ['Total Office Equipment' ] element is not in the 'Capex' cube but in a different cube. Do both cubes have the same structure / the same dimensions (dimension names)?
I assumed the ['Total Office Equipment' ] is a numeric, leaf level element. is this assumption correct?

Michel

[Christian]

Michel,

The Total Office Equipment is a leaf level element in the Depreciation Allocation (target cube), so you are correct.

There is a minor difference in the dimensionality, but should not have any impact. I'll explain in more detail, but you don't have to read unless you want. Portfolio! is a dimension with ~30 elements/portfolios. Portfolio TBD is a "catch all" in the Capex cube because the managers don't allocate the expense to Portfolios, Finance does. They put in a departmental number only. So, I want the numbers in Portfolio TBD to be populated in "all" the portfolios so that total bogey can then be allocated across all the portfolios.

It is worth mentioning again and maybe where I am hung up, that 'Asset Type Cumul' is a string field with a picklist; 'Office Equipment' is an element in that picklist.

Thanks for taking the time to help.

[Michel Zijlema]

There is a minor difference in the dimensionality, but should not have any impact. I'll explain in more detail, but you don't have to read unless you want. Portfolio! is a dimension with ~30 elements/portfolios. Portfolio TBD is a "catch all" in the Capex cube because the managers don't allocate the expense to Portfolios

Hi Christian,

The difference in name is relevant. TM1 does not automatically understand that it should map the 'Portfolio' dimension in the 'Depreciation Allocation' cube to 'Portfolio TBD' dimension in the 'Capex' cube.
In order to do this you need to use the 'Depreciation Allocation' cube internal dimension 'Portfolio' in the reference:

['Total Office Equipment'] = N:
IF(DB('Capex', !Version, !Department, !Country, !Month, !Portfolio, !Capital Exp, 'Asset Type Cumul') @= 'Office Equipment',
DB('Capex', !Version, !Department, !Country, !Month, !Portfolio, !Capital Exp, 'Depreciation Expense on New Purchases'),
STET);


Michel

[Christian]

Thanks Michel...I got it to work!