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
SUMPRODUCT Functionality in TM1 Cube
-
tomok
- MVP
- Posts: 2836
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: SUMPRODUCT Functionality in TM1 Cube
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.
-
mattgoff
- MVP
- Posts: 518
- Joined: Fri May 16, 2008 1:37 pm
- OLAP Product: TM1
- Version: 10.2.2.6
- Excel Version: O365
- Location: Florida, USA
Re: SUMPRODUCT Functionality in TM1 Cube
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
Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
-
anoops81
- Posts: 101
- Joined: Tue May 12, 2009 8:20 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: Office 2010
Re: SUMPRODUCT Functionality in TM1 Cube
Hi Matt ,
We are doing this in TM1 Cube . Thanks a lot . Will try the method .
Regards
Anoop
We are doing this in TM1 Cube . Thanks a lot . Will try the method .
Regards
Anoop
-
Lance_Colins
- Posts: 3
- Joined: Wed Jun 15, 2011 8:54 pm
- OLAP Product: TM1
- Version: TM1
- Excel Version: 2010
Re: SUMPRODUCT Functionality in TM1 Cube
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
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
-
Totti.Ning
- Posts: 6
- Joined: Thu May 17, 2012 2:35 pm
- OLAP Product: Cognos TM1
- Version: 9.5
- Excel Version: 2007
Re: SUMPRODUCT Functionality in TM1 Cube
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.