Excel SumIF in TM1

Post Reply
AlphaWay
Posts: 10
Joined: Wed May 20, 2020 6:23 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Version 2003

Excel SumIF in TM1

Post by AlphaWay » Tue Aug 04, 2020 7:56 pm

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
TIA,
Wayfarer

Wim Gielis
MVP
Posts: 2531
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Excel SumIF in TM1

Post by Wim Gielis » Wed Aug 05, 2020 2:10 am

What is the cube structure ?
What is the structure of the dimension that contains India and SG ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

AlphaWay
Posts: 10
Joined: Wed May 20, 2020 6:23 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Version 2003

Re: Excel SumIF in TM1

Post by AlphaWay » Wed Aug 05, 2020 5:36 am

It's a Measure dimension.

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

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Excel SumIF in TM1

Post by HighKeys » Wed Aug 05, 2020 8:06 am

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

MarenC
Posts: 118
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Excel SumIF in TM1

Post by MarenC » Wed Aug 05, 2020 8:45 am

Hi,

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

Maren

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Excel SumIF in TM1

Post by HighKeys » Wed Aug 05, 2020 9:29 am

As i know, it's more performant if you loop though a view rather then using CellPutN(CellGetN(.....)....);

But yes would also work.

MarenC
Posts: 118
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Excel SumIF in TM1

Post by MarenC » Wed Aug 05, 2020 9:42 am

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

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Excel SumIF in TM1

Post by HighKeys » Wed Aug 05, 2020 10:05 am

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:

AlphaWay
Posts: 10
Joined: Wed May 20, 2020 6:23 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: Version 2003

Re: Excel SumIF in TM1

Post by AlphaWay » Wed Aug 05, 2020 10:07 am

All columns mentioned are from same "Measure" dimension .
SUMIF.JPG
Scenario
SUMIF.JPG (46.9 KiB) Viewed 293 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.
TIA,
Wayfarer

HighKeys
Posts: 97
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Plus 2013

Re: Excel SumIF in TM1

Post by HighKeys » Wed Aug 05, 2020 10:28 am

Please post your TI code.

Post Reply