# Range with MAX

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

• # Range with MAX

Hello,

I would like to calculate the maximum of values from 12 months with the help of a rule. Example (Sales) :

Year 2009 2010

Jan 1000 3000
Feb 2000 2000
Mrz 1000 1500
...
Dec 1500 2000

MAX 2000 3000

How does such a rule works?
Year must be variabel. Please no solution via structure or subset…
thanks in advance.
Greetings from South Germany
Huckly
• # RE: Range with MAX

Hi Huckly,

Assuming you have an element value called 'Year' which is a consolidation of all of the months then something like this may work. Basically this will set the value against the consolidation equal to the maximum sales value acoss the children:

### Source Code

1. ['Year', 'Sales'] = C: MAX(['Jan', 'Sales Price'], ['Feb', 'Sales Price'], ['Mar', 'Sales Price'], ['Apr', 'Sales Price'], ['May', 'Sales Price'], ['Jun', 'Sales Price'], ['Jul', 'Sales Price'], ['Aug', 'Sales Price'], ['Sep', 'Sales Price'], ['Oct', 'Sales Price'], ['Nov', 'Sales Price'], ['Dec', 'Sales Price'])

I hope this helps.

Rod
• Hi Rod,

thank you for your support. Is possible to build into the formula a variable.
E.G. I would like, without changing the formula, switch from Sales to Cost. Can that be done?
Thanks in advance
Greetings from South Germany
Huckly
• Hi Huckly,

I don't think you can place a variable directly into a rule (I stand to be corrected here) but you could drive some conditional logic based on a cell value i.e. an attribute.

For example create an attribute called Max against your measures dimension and place a value of 1 against the Max attribute for Sales and/or Cost.

Then you would need to modify your rule to be something like the following:

### Source Code

1. [Year] = C: IF(PALO.DATA("server", "Cube", "#_Measure", "Max", !'Measure')==1, MAX(['Jan'], ['Feb'], ['Mar'], ['Apr'], ['May'], ['Jun'], ['Jul'], ['Aug'], ['Sep'], ['Oct'], ['Nov'], ['Dec']), STET())

This rule should then calculate the max. value where the attribute is set as 1 else perform a normal consolidation.

I hope this helps but I would be interested if anyone has a more elegant solution.

Rod
• Hi Rod,

thank you again...
you brought me on an idea: is it possible to get the variable from a parallel cube?
Greetings from South Germany
Huckly
• Hi Huckly,

The rule can reference anywhere within the same palo database. To reference another cube just update the PALO.DATA statement that appears after the IF statement in my earlier example.

If you are unsure how to do this the rule editor should be able to help you build this statement.

Hope this helps.

Rod
• Hello Rod,
you helped me very much. Thank you again…
Now I have still another question. How does a Rule have to look, which ignores the zero values when i want to calculate the smallest values (MIN)?
Thanks in advance..
Greetings from South Germany
Huckly
• Hi,

however you try to solve the problem, be aware that palo doesn't treat NULL an ZERO any different, so your min/max functions may not work properly for years with months that haven't been filled yet.
But I think Palo has a setting to override this behaviour in the palo.ini.

regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• Hi tish1,

thank you for this hint. Do you know this setting in palo.ini?

Thank's in advance
Greetings from South Germany
Huckly
• Hi,

check the palo.ini.sample (can be found in the Palo manual as well).

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• I know TM1 has this option (although it is not functioning in a useable way), but AFAIK PALO doesn't have the option to separate NULL from zero.

Michel
• Hi Michel,

looks like you are right and I mixed those two up:

### Source Code

1. Palo Server Version 3.1.1 (801)
2. usage: palo [-?|--help]
3. [-a|--admin <address> <port> ...]
4. [-A|--auto-load]
5. [-b|--cache-barrier <maximum of number_of_cells to store in each Cube cache>]
6. [-B|--auto-commit]
7. [-C|--chdir]
8. [-d|--data-directory <directory>]
9. [-D|--add-new-databases]
10. [-e|--clear-cache <number_of_cache_invalidations>]
11. [-E|--extensions <directory>]
12. [-g|--clear-cache-cells <number_of_cells to purge from cache>]
13. [-G|--ignore-rule-error <arg>]
14. [-h|--http <address> <port> ...]
15. [-H|--https <port> ...]
16. [-i|--init-file <init-file>]
17. [-I|--initial-thread-pool <initial-pool-size> ...]
18. [-K|--key-files <ca> <private> <dh> ...]
19. [-l|--maximum-return-cells <maximum_return_cells>]
20. [-L|--splash-limit <error> <warning> <info> ...]
21. [-M|--session-timeout <seconds>]
22. [-m|--undo-memory-size <undo_memory_size_in_bytes_per_lock>]
23. [-n|--load-init-file]
24. [-o|--log <logfile>]
25. [-O|--maxgrow <maximal-area-grow>]
26. [-p|--password <private-password>]
27. [-Q|--autosave <mode> <hour>:<minute> ...]
28. [-R|--user-login]
29. [-t|--template-directory <directory>]
30. [-u|--undo-file-size <undo_file_size_in_bytes_per_lock>]
31. [-v|--verbose <level>]
32. [-V|--version]
33. [-w|--worker <worker-executable> <argument1> <argument2> <argumentX> ...]
34. [-x|--workerlogin <worker-login-type>]
35. [-X|--encryption <encryption-type>]
36. [-y|--enable-drillthrough]
37. [-Y|--use-cube-worker]
38. [-z|--goalseek-timeout <miliseconds>]
39. [-Z|--goalseek-limit <number_of_cells>]
40. [-c|--cache-size <total_cache_size_in_bytes>]
41. [--processors-cores <number-processors> <number-cores> ...]
42. [-J|--max-subjobs <max-subjobs-size> ...]
43. [-k|--keep-trying]
44. [--parallel-computation]
45. [-r|--rule-cache-size <total_cache_size_in_bytes>]
Display All
Robert Tischler
Senior Consultant Vector SW DV GmbH
• Hi tish1,
Hi Michael,

thank you for your support. Unfortunately I found no way over the Rules. I decided now for a Workaround.
Greetings from South Germany
Huckly