TM1 Forum

Dear all,

I would like the know if you can work with a lookup-function within TM1 Architect (so not in TM1 Web)

Currently we are working on building a cube within TM1 to calculate revenues. These revenues will be calculated in a very simplistic manner: in the rows you can see the type of revenue and in the colums the different measures (Volume, Tarrif and Revenue). The revenue is calculated as Volume*Tariff. Although the calculation looks rather simple, the calculation of the tariff is somewhat difficult. The calculation of the tariff should be linked to the volumes via bands or ranges, each volume range uses a different tarrif .

E.g:
Numbers below represent the tarrifs for the different volume ranges or the applicable tariff for a specific volume:

0 - 100 with tarrif 10
100-200 with tarrif 50
200-300 with tariff 100
300-400 with tariff 1.000

To put the explanation in to a understandable example with given bands/ranges of different volumes and tarrifs:
* Suppose the order volume is 256
* This means that the given volume is with the volume range 200-300
* This indicates the tariff of 100

* Suppos the order volume is 398
* This means that the given volume is within the volume range 200-300
* This indicates the tariff of 1.000

To conclude:
In excel you have an easy way to show the applicable tariff for a specific volume using the VLOOKUP function with an APPROXIMATE match, but my question is if there is also a way to do this within TM1 architect when defining the measures such as volume and tariff (so not in TM1 Web).

Thanks for your help in advance!!

Anthony

Antonius,

There are a number of ways you could go about doing this:

If you really really like the idea of a lookup you could create an extra dim (not included in the cube) that has elements N-Level 0-400 each with an attribute corresponding to the tariff... then have a rule that does an Attrs (or attrn seeming that all the attributes appear to be numbers) based on the result of the volume measure.

Personally though I'm not a huge fan of having dims that are unattached.

You could do a simple IF rule e.g.

IF the Volume measure is between 0 and 100 then 10
ELSEIF volume measure between 101 and 200 then 50
etc
etc
etc

Again there are a number of ways and this would just be 2 options... I would consider whether the Tariff is a measure or whether it could be a dim... I'm by no means saying its wrong to have it as a measure but its just worth consideration.

Hope this gives you a starter.

I wouldn't hard code the tariffs in the rules, I would create a lookup.

1) Create a tarrif dimension, with elements like Tarrif_1, Tarrif_2, Tarrif_3.
2) Add two attributes to the tarrif dimension; Upper_Limit and Rate.
3) Populate Upper_Limit with the upper limit for when a tarrif goes to the next tranche. In your example the Upper_Limit for Tarrif_1 would be 101, Tarrif_2 would be 201, etc.
4) Populate the Rate. Tariff_1 would be 10, etc.
5) Add these rules to your cube, in this order:

['Tarrif'] = N:IF(['Revenue']<ATTRN('Tarrif','Tarrif_1','Upper_Limit'),ATTRN('Tarrif','Tarrif_1','Tarrif_1'),CONTINUE);
['Tarrif'] = N:IF(['Revenue']<ATTRN('Tarrif','Tarrif_2','Upper_Limit'),ATTRN('Tarrif','Tarrif_2','Tarrif_2'),CONTINUE);
['Tarrif'] = N:IF(['Revenue']<ATTRN('Tarrif','Tarrif_3','Upper_Limit'),ATTRN('Tarrif','Tarrif_3','Tarrif_3'),CONTINUE);
['Tarrif'] = N:IF(['Revenue']<ATTRN('Tarrif','Tarrif_4','Upper_Limit'),ATTRN('Tarrif','Tarrif_4','Tarrif_4'),CONTINUE);
...........
['Tarrif'] = N:IF(['Revenue']<ATTRN('Tarrif','Tarrif_x','Upper_Limit'),ATTRN('Tarrif','Tarrif_x','Tarrif_x'),CONTINUE);

Following this logic you can add as many tarrif levels as you want (adding a new rule line as appropriate) and can change the tarrif rates and levels at will. If the tarrifs change by version, or year, you may have to create a cube with those dimensions, instead of a dimension attribute, but the logic is the same.