Excel SumIF in TM1
-
- 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
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
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
Wayfarer
-
- MVP
- Posts: 3120
- 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
What is the cube structure ?
What is the structure of the dimension that contains India and SG ?
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
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
-
- 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
It's a Measure dimension.
Cube has total of 5 dims where "Item" is Row dimension set and "Measure" is Column dimension
Cube has total of 5 dims where "Item" is Row dimension set and "Measure" is Column dimension
TIA,
Wayfarer
Wayfarer
-
- 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
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
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
-
- Regular Participant
- Posts: 350
- 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
Hi,
he might be able to do cellget if the dimension structures allow for it? I thought that was the point of TM1.
Maren
he might be able to do cellget if the dimension structures allow for it? I thought that was the point of TM1.
Maren
-
- 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
As i know, it's more performant if you loop though a view rather then using CellPutN(CellGetN(.....)....);
But yes would also work.
But yes would also work.
-
- Regular Participant
- Posts: 350
- 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
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
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
-
- 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
All columns mentioned are from same "Measure" dimension .
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.
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
Wayfarer
-
- 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
Please post your TI code.