Max Value

[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?

[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

[bihints.com]

Couldn't TOPCOUNT in a MDX expression do?

[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.

[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.

[Steve Vincent]

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.

[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.

[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

[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

[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.

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.

[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]

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, ..., ..., ....);

[PavoGa]

One way I can think of is if you have a rolling 24 consolidation, then ConsolidatedMax should be able to do it.

[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