Max Value
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Max Value
Simple enough, but rules don't want to do it
I have a 4d cube and a need to find the maximum value across one dim of hours (00 to 23). MAX only finds the biggest value between 2, so how do i find the max across all 24?
I know i could consolidate each hour in to pairs, then pairs of those pairs etc with a max formula for each but thats stupid, messy and there has got to be a better way of doing it. I could TI the value in to a dummy element but i'm trying to avoid doing that...
Any ideas?
I have a 4d cube and a need to find the maximum value across one dim of hours (00 to 23). MAX only finds the biggest value between 2, so how do i find the max across all 24?
I know i could consolidate each hour in to pairs, then pairs of those pairs etc with a max formula for each but thats stupid, messy and there has got to be a better way of doing it. I could TI the value in to a dummy element but i'm trying to avoid doing that...
Any ideas?
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
- John Hobson
- Site Admin
- Posts: 330
- Joined: Sun May 11, 2008 4:58 pm
- OLAP Product: Any
- Version: 1.0
- Excel Version: 2020
- Location: Lytham UK
- Contact:
Re: Max Value
Oddly this came up on Jedox's own Palo forum this week too
The max(a,b) function returns the max value of the specified parameters a or b (as you know)
This means that you can use it to test which of two or more specified numbers is the greater but you can just say max(all children of consolidation x) which is I think what you are trying to do.
There are ways to force a consolidation to be the maximum of its children by using another variable and populating that with a sequential max test of all the children of the original variable (so that the last carries the max value)
This would go something like:
If trying to calculate max hours then create new variable"maxhours"
If it's the first child then max (this child, 0)
For all other children max (this child, previous child)
C: level for "hours" = Last Child of C: Level of "maxhours"
This is a very recursive type of calculation with every calculation dependent on the previous ones. As a result you may find it runs out of stack space when you have a large number of child elements
Hope this helps
John
PS because of the recursion issue I ended up having to use a TI when I got over about 300 elements
The max(a,b) function returns the max value of the specified parameters a or b (as you know)
This means that you can use it to test which of two or more specified numbers is the greater but you can just say max(all children of consolidation x) which is I think what you are trying to do.
There are ways to force a consolidation to be the maximum of its children by using another variable and populating that with a sequential max test of all the children of the original variable (so that the last carries the max value)
This would go something like:
If trying to calculate max hours then create new variable"maxhours"
If it's the first child then max (this child, 0)
For all other children max (this child, previous child)
C: level for "hours" = Last Child of C: Level of "maxhours"
This is a very recursive type of calculation with every calculation dependent on the previous ones. As a result you may find it runs out of stack space when you have a large number of child elements
Hope this helps
John
PS because of the recursion issue I ended up having to use a TI when I got over about 300 elements
John Hobson
The Planning Factory
The Planning Factory
- bihints.com
- Posts: 52
- Joined: Tue May 20, 2008 8:56 am
- OLAP Product: TM1
- Version: 9.0.3
- Excel Version: 2003
- Contact:
- John Hobson
- Site Admin
- Posts: 330
- Joined: Sun May 11, 2008 4:58 pm
- OLAP Product: Any
- Version: 1.0
- Excel Version: 2020
- Location: Lytham UK
- Contact:
Re: Max Value
I imagine it could, but can you access the resulting value via rules?
If so how - I have to confess I've never thought of doing that and don't know how I'd start.
If so how - I have to confess I've never thought of doing that and don't know how I'd start.
John Hobson
The Planning Factory
The Planning Factory
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Re: Max Value
indeed, MDX crossed my mind but i need the value to appear in the cube against the same element each time, as far as i could see MDX would only select the element with the biggest value in it, which could be something different each time.
ie. the cube has time across top and date down side. I wanted a matrix that told me the max value per day, based on the values it held per hour. I have a TI solution which is fine, but i did want to avoid extra elements just to try and keep things tidy / simple.
ie. the cube has time across top and date down side. I wanted a matrix that told me the max value per day, based on the values it held per hour. I have a TI solution which is fine, but i did want to avoid extra elements just to try and keep things tidy / simple.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
- Steve Vincent
- Site Admin
- Posts: 1054
- Joined: Mon May 12, 2008 8:33 am
- OLAP Product: TM1
- Version: 10.2.2 FP1
- Excel Version: 2010
- Location: UK
Re: Max Value
Thats the problem tho, it ONLY compares 2 values, no more, no less. I was quite prepared to have a 24 part MAX statement but it won't let you do that, hence looking for other options.John Hobson wrote:This means that you can use it to test which of two or more specified numbers is the greater but you can just say max(all children of consolidation x) which is I think what you are trying to do.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
- John Hobson
- Site Admin
- Posts: 330
- Joined: Sun May 11, 2008 4:58 pm
- OLAP Product: Any
- Version: 1.0
- Excel Version: 2020
- Location: Lytham UK
- Contact:
Re: Max Value
But f you make it relative using a elcomp then it will compare ALL the child elements and then the last child will be the max.Thats the problem tho, it ONLY compares 2 values, no more, no less.
John Hobson
The Planning Factory
The Planning Factory
-
- MVP
- Posts: 263
- Joined: Fri Jun 27, 2008 12:15 am
- OLAP Product: Cognos TM1, CX
- Version: 9.0 and up
- Excel Version: 2007 and up
Re: Max Value
Hi John,
I am curious how exactly you would make the elements relative in the consolidation. For elcomp you actually need the index of the element in the consolidation for it to return an element name but I cannot find a function which will return the index in a consol (only in the dimension).
So how would you get the previous element in a consolidation?
Cheers
I am curious how exactly you would make the elements relative in the consolidation. For elcomp you actually need the index of the element in the consolidation for it to return an element name but I cannot find a function which will return the index in a consol (only in the dimension).
So how would you get the previous element in a consolidation?
Cheers
- John Hobson
- Site Admin
- Posts: 330
- Joined: Sun May 11, 2008 4:58 pm
- OLAP Product: Any
- Version: 1.0
- Excel Version: 2020
- Location: Lytham UK
- Contact:
Re: Max Value
Assuming that element indices are on order within the consolidation then
['Min Space' ] = C: IF (dimnm('Store',dimix('Store',!store)) @= 'All Base Stores',
DB('AutoGrade', !Version, !Prod_D, !Time Season, dimnm('Store',elcompn('Store','All Base Stores')),'Min Space'),
DB('MinMax',!Version,!Prod_D,!Time Season,'Min Space'),
0);
If not then I think you are stuffed
['Min Space' ] = C: IF (dimnm('Store',dimix('Store',!store)) @= 'All Base Stores',
DB('AutoGrade', !Version, !Prod_D, !Time Season, dimnm('Store',elcompn('Store','All Base Stores')),'Min Space'),
DB('MinMax',!Version,!Prod_D,!Time Season,'Min Space'),
0);
If not then I think you are stuffed
John Hobson
The Planning Factory
The Planning Factory
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Max Value
These came along long after this thread, but I'm going to post them here in case anyone stumbles onto the thread through a search. After which, I'll FAQ the thing.
TM1 TurboIntegrator functions ConsolidatedMax; ConsolidatedMin; ConsolidatedAvg; ConsolidatedCount; and Consolidated CountUnique are available.
TM1 TurboIntegrator functions ConsolidatedMax; ConsolidatedMin; ConsolidatedAvg; ConsolidatedCount; and Consolidated CountUnique are available.
Technote wrote:Problem(Abstract)
Need TurboIntegrator and Rules processes to perform minimum, maximum, average and unique counts.
Resolving the problem
ConsolidatedMax; ConsolidatedMin; ConsolidatedAvg; ConsolidatedCount
These functions calculate the maximum, minimum, average or count across all the elements in a consolidation and returns a single value. These functions can be used in TurboIntegrator processes or Rules.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- 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: Max Value
Hi,
does anyone know how to get the maximum value by looking at the previous, say 24 months, from another cube?
So for example in cube A January 2019, how to get the maximum value for the previous 24 months (from January 2019) from Cube B (which includes months dimension too).
In other words how can consolidated max be made dynamic in this way? Any ideas?
Maren
does anyone know how to get the maximum value by looking at the previous, say 24 months, from another cube?
So for example in cube A January 2019, how to get the maximum value for the previous 24 months (from January 2019) from Cube B (which includes months dimension too).
In other words how can consolidated max be made dynamic in this way? Any ideas?
Maren
-
- Posts: 139
- Joined: Tue May 21, 2019 3:33 pm
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 2016
- Location: The Internet
Re: Max Value
Hi,
I don't know about the Consolidated Max but maybe this can help.
For one of my client, I had to dynamically retrieve the maximum value in a cube. My suggestion is to create a simple TI and do something like this:
I don't know about the Consolidated Max but maybe this can help.
For one of my client, I had to dynamically retrieve the maximum value in a cube. My suggestion is to create a simple TI and do something like this:
Code: Select all
#====================
# Prolog ( DataSourceType = 'View';)
#====================
nMaxValue = 0;
#===================
# Data
#===================
IF( Value > nMaxValue );
nMaxValue = Value;
ENDIF;
#===================
# Epilog
#===================
CellPutN( nMaxValue, ..., ..., ....);
- PavoGa
- MVP
- Posts: 617
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Max Value
One way I can think of is if you have a rolling 24 consolidation, then ConsolidatedMax should be able to do it.
Ty
Cleveland, TN
Cleveland, TN
-
- 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: Max Value
Hi,
Rolling consolidations just seem like a maintenance nightmare, so parked that idea for now.
The TI process seems reasonable, i.e. you could have a nightly process to update the current period.
But have gone with the following for now:
1. Create a new numeric attribute called Index Period, which numbers the periods from 1 to n. The year and period are in a single dimension, which helps with the solution! So let us say the first year is 2015, element 201501 would be Index Period 1, 201502 would be Index Period 2 etc etc.
2. Create a rule by nesting the MAX function 24 times, an example of the code is below, which does it for 4 periods:
3. Create Feeder. Feeder looks something like the following:
Does the above sound like a workable solution, it seems to be working on my test data?
Maren
Rolling consolidations just seem like a maintenance nightmare, so parked that idea for now.
The TI process seems reasonable, i.e. you could have a nightly process to update the current period.
But have gone with the following for now:
1. Create a new numeric attribute called Index Period, which numbers the periods from 1 to n. The year and period are in a single dimension, which helps with the solution! So let us say the first year is 2015, element 201501 would be Index Period 1, 201502 would be Index Period 2 etc etc.
2. Create a rule by nesting the MAX function 24 times, an example of the code is below, which does it for 4 periods:
Code: Select all
['MaxValue']=N:
Max(Max(Max(DB('CubeB', str(attrn('Periods', !Periods, 'Index Period')-1,6,0), 'TestAccount','Sales'),
DB('CubeB', str(attrn('Periods', !Periods, 'Index Period')-2,6,0), 'TestAccount','Sales')),
DB('CubeB', str(attrn('Periods', !Periods, 'Index Period')-3,6,0), 'TestAccount','Sales')),
DB('CubeB', str(attrn('Periods', !Periods, 'Index Period')-4,6,0), 'TestAccount','Sales'));
Code: Select all
['Sales']=>DB('CubeA', str(attrn('Periods', !Periods, 'Index Period')+1,6,0), 'All Items', 'MaxValue');
['Sales']=>DB('CubeA', str(attrn('Periods', !Periods, 'Index Period')+2,6,0), 'All Items', 'MaxValue');
['Sales']=>DB('CubeA', str(attrn('Periods', !Periods, 'Index Period')+3,6,0), 'All Items', 'MaxValue');
['Sales']=>DB('CubeA', str(attrn('Periods', !Periods, 'Index Period')+4,6,0), 'All Items', 'MaxValue');
Maren