Max Value

Post Reply
User avatar
Steve Vincent
Site Admin
Posts: 1050
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 » Wed Nov 12, 2008 1:49 pm

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: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

User avatar
John Hobson
Site Admin
Posts: 329
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007
Location: Lytham UK
Contact:

Re: Max Value

Post by John Hobson » Wed Nov 12, 2008 1:57 pm

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

TM1 10.2.0 / Win7 / XL 2010

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 » Wed Nov 12, 2008 2:09 pm

Couldn't TOPCOUNT in a MDX expression do?

User avatar
John Hobson
Site Admin
Posts: 329
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007
Location: Lytham UK
Contact:

Re: Max Value

Post by John Hobson » Wed Nov 12, 2008 3:10 pm

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

TM1 10.2.0 / Win7 / XL 2010

User avatar
Steve Vincent
Site Admin
Posts: 1050
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 » Wed Nov 12, 2008 4:00 pm

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: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

User avatar
Steve Vincent
Site Admin
Posts: 1050
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 » Wed Nov 12, 2008 4:07 pm

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: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

User avatar
John Hobson
Site Admin
Posts: 329
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007
Location: Lytham UK
Contact:

Re: Max Value

Post by John Hobson » Wed Nov 12, 2008 4:26 pm

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

TM1 10.2.0 / Win7 / XL 2010

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 » Wed Oct 14, 2009 12:13 am

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: 329
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007
Location: Lytham UK
Contact:

Re: Max Value

Post by John Hobson » Wed Oct 14, 2009 6:18 am

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

TM1 10.2.0 / Win7 / XL 2010

User avatar
Alan Kirk
Site Admin
Posts: 5998
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Max Value

Post by Alan Kirk » Sun Nov 24, 2013 4:05 am

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
Posts: 52
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 » Tue Oct 29, 2019 11:20 am

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: 40
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: TM1 10.2
Excel Version: 2016

Re: Max Value

Post by Emixam » Tue Oct 29, 2019 12:36 pm

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
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: Max Value

Post by PavoGa » Tue Oct 29, 2019 6:08 pm

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
Posts: 52
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 » Wed Nov 06, 2019 10:52 am

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