Max Value

Post Reply
User avatar
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

Post by Steve Vincent »

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?
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
User avatar
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

Post by John Hobson »

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
John Hobson
The Planning Factory
User avatar
bihints.com
Posts: 52
Joined: Tue May 20, 2008 8:56 am
OLAP Product: TM1
Version: 9.0.3
Excel Version: 2003
Contact:

Re: Max Value

Post by bihints.com »

Couldn't TOPCOUNT in a MDX expression do?
User avatar
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

Post by John Hobson »

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.
John Hobson
The Planning Factory
User avatar
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

Post by Steve Vincent »

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.
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
User avatar
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

Post by Steve Vincent »

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.
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.
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
User avatar
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

Post by John Hobson »

Thats the problem tho, it ONLY compares 2 values, no more, no less.
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.
John Hobson
The Planning Factory
Gregor Koch
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

Post by Gregor Koch »

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
User avatar
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

Post by John Hobson »

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 :D
John Hobson
The Planning Factory
User avatar
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

Post by Alan Kirk »

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.
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.
MarenC
Regular Participant
Posts: 341
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Max Value

Post by MarenC »

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
Emixam
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

Post by Emixam »

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:

Code: Select all

#====================
# Prolog ( DataSourceType = 'View';)
#====================

nMaxValue = 0;

#===================
# Data
#===================

IF( Value > nMaxValue );
	nMaxValue = Value;
ENDIF;

#===================
# Epilog
#===================

CellPutN( nMaxValue, ..., ..., ....);
User avatar
PavoGa
MVP
Posts: 612
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: Cleveland, Tennessee

Re: Max Value

Post by PavoGa »

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
MarenC
Regular Participant
Posts: 341
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Max Value

Post by MarenC »

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:

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'));
3. Create Feeder. Feeder looks something like the following:

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');
Does the above sound like a workable solution, it seems to be working on my test data?

Maren
Post Reply