# Use rule MAX

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Use rule MAX

Hello to all, I am new in the forum and go three weeks knowing PALO and preparing a model example to evaluate its power and functioning.

Till now I have not had big problems, could have solved all of them with the manual and other threads of this forum, but now I have a case that I do not manage how resolve.

It treats itself that I have a variable that it contains "days" and that on having accumulated to a top level it does not see the sum but I must consider the maximum of the dependent values. For it I use the function (rule) MAX but I do not achieve that it works.

Someone might indicate an example to me of how doing it.

Thank you very much.

Regards.
-------------------
Julian del Valle
-------------------
• # RE: Use rule MAX

Hmm I can't find any reference to the max or min functions in the rules guide

However, if it works like TM1 (and there is a fair chance it does) then the max(a,b) function returns the max value of the specified parameters a or b.

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 region 1) which is I think what you are trying to do.

This is generally a difficult thing in OLAP databases. I wonder if the forthcoming MDX stuff will help us here?

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 sales then craete new variable"maxsales"

If it's the first child then max (this child, 0)
For all other children max (this child, previous child)

C: level for "sales" = Last Child of C: Level of "maxsales"

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
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• # RE: Use rule MAX

Thank you John for your response.

Excuse me not to have answered before but I have been out of the office.

I am going to try to solve it like you indicate though I do not see it very clear.

Regards.
-------------------
Julian del Valle
-------------------
• Hers' an example that uses TM1 syntax so you will have to "translate" the function names into PALO speak

At the N: level it makes "MaxSpace" for each child the max of it's own "Space" or that of it's predecessor in the dimension.

At the C: level it makes the Max Space Total Stores = the last member of the consolidation.

N.B. There is an assumption here that the elements within "All Stores" are indexed in the same order as the dimension!!!

### Source Code

1. ['Max Space'] = N: IF(dimix('Store',!Store)=1,
2. DB('Grading', !Version, !Product SG, !Time Season, !STORE, 'Space'),
3. Max(DB('Grading', !Version, !Product SG, !Time Season, !STORE,'Space'), DB('Grading', !Version, !Product SG, !Time Season, dimnm('Store',dimix('Store',!STORE)-1),'Max Space')));
4. ['Max Space'] = C: IF (dimnm('Store',dimix('Store',!store)) @= 'All Stores', DB('Grading', !Version, !Product SG, !Time Season, Dimnm('Store',elcompn('Store','All Base Stores')),'Max Space'), DB('MinMax',!Version,!Product SG,!Time Season,'Max Space'), 0);

Is that any clearer?
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• Thank you John,

The method to using with your example this one clearly, the problem I have it at the moment of converting the functions of TM1 to PALO.

I suppose that "dimix" is the index of a dimension, but I do not know its equivalence in PALO. "PALO.ECHILDCOUNT" or "PALO.ECOUNT" do not return the value that I need and do not see other functions to do this.

And I do not locate more wide documentation.

I follow trying it, already I will say some thing to you.

Thank you very much for your help.

Regards.
-------------------
Julian del Valle
-------------------
• Julian

I'm sorry but I'm not near a Palo computer just now whcih si why I wasn't able to post proper Palo code.

From memory I think the Dimix equivalent is palo.index or palo.eindex

I think you can download a free rule function guide from the Jedox web site

hpoe this helps
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• John,

I have manual "Guidelines Enterprise Rules" for the version PALO 2.o, but I have not just seen as there work the functions PALO.xxx.

Do not worry I will continue trying it.

Thank you for everything.

Regards.
-------------------
Julian del Valle
-------------------
• Julian,
they have pretty much the same syntax as the excel functions. Here's an example:

PALO.EINDEX ("Demo","Products","Desktop L")
==>hard coded for the index of Desktop L Element in product dimension of Demo Database

PALO.EINDEX ("Demo","Products",!'Products')
==>for whatever product is affected

Hope this helps.

Holger

The post was edited 3 times, last by h_decker ().

• Thanks John, thanks Holger,

I have continued testing and reading the manual and already "almost I dominate" the functions PALO.xxx, but I do not manage to do what I want.

The problem I have it in that in a rule, the field in the target area cannot to be index in the source area of the rule.

For example: ['Aux days'] = MAX(['Aux days'],['ok days'])

It does not work, returns mistake #NULL, since ['Aux days'] it is in both sides of the rule.

Is it certain this?

On the other hand I do not finish to see like to have access to the elements subordinated of a dimension in phase of consolidation.

I have the dimensions "Years", "Month", "Regions", "Products", "Measures" and inside "Measures" for example "ok days". I wish that to level of "Regions" (West, East..., Europe..) to have the maximum value of the measured "ok days".

- Years
-- - Month
---- - Europe
-------- - West
----------- - Germany
----------- - France
----------- - Belguium
----------- - .....
-------- - East
----------- - Poland
----------- - ...

Can you help me?.

Thank you very much.

Regards.
-------------------
Julian del Valle
-------------------

The post was edited 2 times, last by jdelValle ().

• You need to use a fully qualified palo.data reference.

I have tried to make the example more like your example and use palo syntax - as I have typed longhand there may be be some errors, wrong quotes etc but it should give you an idea.

This example would only calculate the max of a consolidation "All Regions" that has a list of N levels only in it

To achieve a max consolidation at indeterminate intermediate levels would be a bit more difficult - I wonder if anyone would like to take up the challenge

['Max OK Days'] = N: IF(palo.eindex("Region",!'Region')=1,
palo.data("Server Name", "DBNAme", !'Dimnm1', !'Dimname 2', etc, !Region, 'OK Days'),
Max(palo.data("Server Name", "DBName", !'Dimnm1', !'Dimname 2', etc, !Region,'Space'),palo.data("Server Name", "DBName", !'Dimnm1', !'Dimname 2', etc, palo.ename("dbname","Region",palo.eindex("DBName","Region",!Region)-1),'Max OK Days')));

The above makes the first MAx OK days = the first OK Days and then tests each other OK DAys against te previous elements Max OK Days. As a result the last elements MAx OK days is the maximum value

['Max OK Days'] = C: IF (palo.ename("DBName", Region"',palo.eindex("Dbname","Region",!Region)) == "All Regions", palo.data("ServerName", DBName", "DBName", !'Dimnm1', !'Dimname 2', etc, palo.ename("ServerName", "Region",Palo.echildcount("Dbname"',"Region","All Regions')),'Max OK Days'), 0)

The above makes the value for All Regions = the Max OK days for the last component of All Regions which should be the Max Value base on the N: Rule.

NB If the components in the consolidation were indexed in a different order from the standard index this might not work!
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk

The post was edited 3 times, last by Pommie ().

• Julian,
maybe Subsets are what you need. Have a look at the attached exmple that shows the Top n regions for Turnover in 2006 (Sales cube Demo).
I wouldnt recommend to do this by rules.

Holger
Files
• Holger

I wouldnt recommend to do this by rules.

In TM1 I think most people end up using a turbointegrator process to get round these issues.

You could achieve something similar using VBA in PALO but it wouldn't be a lot prettier.

Sometimes though you need a dynamic solution - particularly in statistical type applications.

Maximum /minimum / average type consolidations would be seriously useful!

Can subsets like the one you have in that sheet be accessed in rules and if so how?

(And should your array formula be referencing the 10 in C1 - I couldn't work out how to select the entire array to try to change it)
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• John,
I didnt try to use them in Rules before but I think it is not possible to use them in a way like an dimension, e.g. with ENAME.

Sorry If I forgot to set the reference on the 10. I attached a new version......and yes it's very annoying to change matrix formulas. The easiest way is to click on the fx symbol in formular bar after that you can change the formula and apply by clicking on OK in formula help dialogue.

Holger
Files
• Thanks Holger

That sub set formula stuff looks amazingly powerful but it does takes a bit of getting to know

I was trying to replicate what you did there but I couldn't get it to work.

After a bit of detective work I notice that the PALO.SORT formula had a 1 as the the first parameter (for WHOLE). If I am honest the description of this parameter ..

0/empty = order by definition. 1 = Builds a hierarchic order and shows the children of elements, the elements which have been removed from the subset. It is a prerequisite that the children are contained in the subset. 2 = Will not show the children, but will cut the view at those particular positions.

.. doesn't mean a great deal to me ( this is true of more than one of the parameter definitions :P)

If I manually edited this to a 0 in the array formula then it works, but I couldn't find a way of changing this from one in the subset wizard.

=PALO.SUBSET("localhost/Demo","Regions",2,,PALO.HFILTER(,FALSE,FALSE,2),,,PALO.DFILTER(PALO.SUBCUBE("Sales","Desktop L",,"Jan","2008","Work in progress","Units"),,10,,,0),,PALO.SORT(0,1,,1,,2,1))

So I have 2 questions

1. Is there a way of doing this via the editor wizard
2. Why would a 1 not work here anyway

Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• John,
I had exactly the same problem of understanding where to change that first sort parameters. You can do that in TAB "General"->Hierarchy (Flat, Hiearchy or Show parents below children).

Second:
1 does not work beacuse I am using sort Filter. 1 is hierarachy so it would sort per Region (North, West,...). Add the one and you will see that
Germany,
France,
United Kingdom,
Belgium,
Switzerland

are sorted within their Region West.

Holger
• Holger

Many thanks!

I'm glad it's not just me then.

I must say though that reading the subset documentation in the manual leaves me feeling rather inadequate!

It's obviously REALLY powerful, but I can't help feeling that it needs a whole manual to itself
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• I think best thing is to create a sheet with combination of some filters you obviously need a lot, parametrize them and play around with the subset of a well known dimension. This is defintly something to learn by doing. I didn't even try to read the documentation because it is too much to remember.

The post was edited 1 time, last by h_decker ().