Page 1 of 1

Excel SumIF in TM1

Posted: Tue Aug 04, 2020 7:56 pm
by AlphaWay
Hi All,

I got a requirement of SUMIF calculation to be done in TI process as below ,

For Eg:
Column A | Column B | Column C | Column D
India | 5 |10 | Calculated Value
SG | 10 | 20 | Calculated Value
SG | 10 | 25 | Calculated Value
India | 45 | 15 | Calculated Value

Column D(Calculation formula)
=(SUMIF($Column A$1:$Column A$4,Column A,$Column B$1:$Column B$4)/SUMIF($Column A$1:$Column A$4,Column A,$Column C$1:$Column C$4))

I understood that the result should be,
India ,50 / India , 25 = Value
SG , 20 / SG , 45 = Value
------------------------------
India = 2
SG = 0.44


Kindly suggest me to achieve in TI Process calculations.

TIA

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 2:10 am
by Wim Gielis
What is the cube structure ?
What is the structure of the dimension that contains India and SG ?

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 5:36 am
by AlphaWay
It's a Measure dimension.

Cube has total of 5 dims where "Item" is Row dimension set and "Measure" is Column dimension

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 8:06 am
by HighKeys
Hi,

first you have to create a view then subsets for all dim's, after this you can loop though the view and calculate what you want.

For sum if just is an If, like

If(vDimforColA @='India);
nSumIf = nSumIf + 'India';
EndIf;


best Regards

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 8:45 am
by MarenC
Hi,

he might be able to do cellget if the dimension structures allow for it? I thought that was the point of TM1.

Maren

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 9:29 am
by HighKeys
As i know, it's more performant if you loop though a view rather then using CellPutN(CellGetN(.....)....);

But yes would also work.

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 9:42 am
by MarenC
Hi Highkeys,

well that depends on how the dimensions are structured I would think. If he just needs the elements from dimension A and the rest can be hard coded values, so consolidations from the other dimensions and a measure, I wouldn't see any need for a loop as such.

If the if part is sumif is for specific attributes or something else not mentioned then yes views and loops might be required.

But generally speaking wanting the total sum of an element in a cube for a given measure is what TM1 is designed for, at least in my mind it is!

Maren

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 10:05 am
by HighKeys
MarenC wrote: Wed Aug 05, 2020 9:42 am ....
But generally speaking wanting the total sum of an element in a cube for a given measure is what TM1 is designed for, at least in my mind it is!

Maren
Yea totally agree! :geek:

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 10:07 am
by AlphaWay
All columns mentioned are from same "Measure" dimension .
Scenario
Scenario
SUMIF.JPG (46.9 KiB) Viewed 3940 times
Sum If function inside TI process is not working.

Inv % column is my result column and mentioned formula is the one i am trying out.

Re: Excel SumIF in TM1

Posted: Wed Aug 05, 2020 10:28 am
by HighKeys
Please post your TI code.