Need to implement excel formula in TM1 Cube

[anoops81]

Hi All ,

I need to implement the following excel formula as a measure dim element in tm1 cube . The calculation references different excel tabs using Vlookups and match function to pull this information.

Code: Select all

High = IF(F/ARM="A","",VLOOKUP(Origination Date,'Market Data'!$A:$IC,MATCH(VLOOKUP(High Term,Fixed Pricing Tables!$Q$7:$AB$15,IF(ISERROR(MATCH($H32,Fixed Pricing Tables!$R$5:$AB$5,0)),1,MATCH($H32,Fixed Pricing Tables!$R$5:$AB$5,0))+1),'Market Data'!$2:$2,0)+1,0))

I was thinking to creating lookup cube for all the tables which are provided in the formula and then write rules to pull the needed info . But dont know how to deal with match function which returns the relative position of elements in the array .

Please tell me this is going to happen in tm1 or not .

Any suggestions are always welcome .

Regards,
Anoop

[lotsaram]

have you considdered that using rules with the !dimension notation has the same effect of checking for an exact match? Similarly if you need to do a one-dimensional lookup for some sort of translation or offset you can achieve the same thing with an attribute as you might achieve with a VLOOKUP in Excel (e.g. AttrS('Dimension', !Dimension, 'Attribute')).

I think there's no reason why you couldn't model the same calculation logic in TM1. But rather than looking at the formula in Excel it is better to look at the actual intent of the calculation and the requirements and THEN look at how to do it in TM1 without your analysis being framed or distorted by how it is currently achieved in Excel (which might not be the best or most efficient way BTW as there would be literally 100s if not 1000s or other Excel formulas that might achieve the same result and some will be better than others.)

[anoops81]

Thanks lotsaram .

I will check with the business on the actual requirement .

Regards,
Anoop

[David Usherwood]

A good idea
I and others have suffered quite a bit from forumers who insist they _must_ do something without finding out _why_.
Having said that, when I have to work with a complex model, I base it on _both_ a functional spec _and_ an Excel worked example, if at all possible.