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 :P
      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 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 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
    • 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.
    • 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