TM1 Technical question

[Totti.Ning]

Dear everybody

how does tm1 can implete triangle fuction caculation like excel SUMPRODUCT fuction ? ? ?


thx.

[Andy Key]

You can find details on how to do a SumProduct here.

[Duncan P]

Looking at the documentation of SUMPRODUCT (http://office.microsoft.com/en-us/excel ... 42935.aspx) it is actually much simpler. SUMPRODUCT is defined as the sum over the ranges specified of the pairwise products of the cells in the ranges. This is just normal consolidation of an N: level product measure.

Suppose each range in your SUMPRODUCT corresponds to a measure in your cube and that all the other dimensions in the cube have a "Total" item which is the sum of all the leaves. Create a new measure PRODUCT and set the rule

Code: Select all

['PRODUCT'] = N: ['Measure1'] * ['Measure2'] * ['Measure3'];

The value for "Total Dim1", "Total Dim2", ... "PRODUCT" will be the sum of all the products.

The key here is the N: which tells TM1 only to apply the product to the leaves and to let the totals be done by normal summation.