Page 1 of 1
SUMPRODUCT Functionality in TM1 Cube
Posted: Fri Jun 10, 2011 12:22 pm
by anoops81
Hi ,
We have a requirement , where the client wants to see the SUMPRODUCT ( array1 ,array2, ........arrayn) in tm1 cube . SUMPRODUCT is an excel function which multiplies the corresponding items in the arrays and returns the sum of the results. We would like to implement this functionality in tm1 cubes.
I searched in IBM Infocenter and found that SUMPRODUCT it is an unsupported excel function in tm1 web .
Your suggestions are most welcome !!!
Regards
Anoop
Re: SUMPRODUCT Functionality in TM1 Cube
Posted: Fri Jun 10, 2011 1:06 pm
by tomok
It's not supported in TM1 because it simply doesn't make any sense in an OLAP environment! How could you possibly have an array in a cube other than a grouping of elements like that defined by a consolidation????? You can only store one value in a cell in TM1, not an array. Technically you could define an element as a string and then you could store alphanumeric data separated by a comma (which would look like an array), something like '1,23,44,533,2,233' but since it's a string TM1 can't do math on it. You would have to parse out the commas, convert to numbers and add. Just doesn't make any sense.
Re: SUMPRODUCT Functionality in TM1 Cube
Posted: Fri Jun 10, 2011 1:45 pm
by mattgoff
Anoop, it's not clear from your post if you're doing this in TM1 or TM1 Web. For TM1, create a new measure that multiplies your two other measures (via rule) and use a consolidation to sum. In TM1 Web (Excel), create a hidden column that multiplies the two values and sum that column.
Matt
Re: SUMPRODUCT Functionality in TM1 Cube
Posted: Fri Jun 10, 2011 2:03 pm
by anoops81
Hi Matt ,
We are doing this in TM1 Cube . Thanks a lot . Will try the method .
Regards
Anoop
Re: SUMPRODUCT Functionality in TM1 Cube
Posted: Wed Jun 15, 2011 9:04 pm
by Lance_Colins
Hi Anoop,
The purpose of using the SumProduct is to isolate for example a given product, in given country, in a given time period. It is used in excel as a way to slice and dice the information, at least that is how I use it.
As long as you have the dimensions broken out as to how the user would like to isolate (or slice and dice) the data then the consolidation will do the work within the cube. Therefore, the user can look at the given product dimension, given country dimension and given time period to find their answer.
Hope that helps
Re: SUMPRODUCT Functionality in TM1 Cube
Posted: Fri Jul 06, 2012 3:34 am
by Totti.Ning
mattgoff wrote:Anoop, it's not clear from your post if you're doing this in TM1 or TM1 Web. For TM1, create a new measure that multiplies your two other measures (via rule) and use a consolidation to sum. In TM1 Web (Excel), create a hidden column that multiplies the two values and sum that column.
Matt
So cute method Matt....
thx.