TM1 formulas and array formulas
Posted: Sat Dec 14, 2013 10:59 pm
Just out of curiosity... has anyone ever played around with the combination of TM1 formulas and array formulas in Excel?
The latter being defined as "they have to be entered with Control-Shioft-Enter, rather than Enter only".
For example, with 1 formula, calculate the maximal number of elements over all dimensions:
=MAX(DIMSIZ("CXMD:" & DIMNM("CXMD:}Dimensions",ROW(INDIRECT("1:" & DIMSIZ("CXMD:}Dimensions"))))))
... entered with CSE (Ctrl-Shift-Enter) so that curly braces ( { } ) appear around the formula in the formula bar. But it doesn't work...
If you have 5 dimensions in the TM1 model, the bit with ROW(...) correctly evaluates to the array {1, 2, 3, 4, 5}
But supplying this array to the DIMNM function as the "index" (now an array of indexes) will leave us with only the first element in the array (index 1) and hence, get the maximum of only 1 dimension's elements.
Anyone done this before?
Wim
The latter being defined as "they have to be entered with Control-Shioft-Enter, rather than Enter only".
For example, with 1 formula, calculate the maximal number of elements over all dimensions:
=MAX(DIMSIZ("CXMD:" & DIMNM("CXMD:}Dimensions",ROW(INDIRECT("1:" & DIMSIZ("CXMD:}Dimensions"))))))
... entered with CSE (Ctrl-Shift-Enter) so that curly braces ( { } ) appear around the formula in the formula bar. But it doesn't work...
If you have 5 dimensions in the TM1 model, the bit with ROW(...) correctly evaluates to the array {1, 2, 3, 4, 5}
But supplying this array to the DIMNM function as the "index" (now an array of indexes) will leave us with only the first element in the array (index 1) and hence, get the maximum of only 1 dimension's elements.
Anyone done this before?
Wim