Excel SumIF in TM1

Post Reply
AlphaWay
Posts: 11
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 »

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: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Excel SumIF in TM1

Post by Wim Gielis »

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

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
AlphaWay
Posts: 11
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 »

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: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: Excel SumIF in TM1

Post 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
MarenC
Regular Participant
Posts: 346
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 »

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: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: Excel SumIF in TM1

Post 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.
MarenC
Regular Participant
Posts: 346
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 »

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: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: Excel SumIF in TM1

Post 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:
AlphaWay
Posts: 11
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 »

All columns mentioned are from same "Measure" dimension .
Scenario
Scenario
SUMIF.JPG (46.9 KiB) Viewed 3936 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: 117
Joined: Fri Aug 09, 2019 10:11 am
OLAP Product: TM1 / TM1 Web / Perspectives
Version: Planning Analytics V2.0.9
Excel Version: Office 365

Re: Excel SumIF in TM1

Post by HighKeys »

Please post your TI code.
Post Reply