Calculate previous element

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

    • Calculate previous element

      Hi all,

      I have the following rule:

      ['Januar', '2009'] = 0,5 * ['Dezember', '2008']

      First of all: that works just fine. But...

      Since I do have 1996-2015 in my year Dimension (and there will be more) I do not want to do that role for each of the years. Is there a possibility to make one rule that applies the same operation each end of the year?

      Sem goes for the following:

      ['Februar'] = 0,5 * ['Januar']

      Since I don't need the year here it would just be 11 rules - but I'd rather have one for all. Possible?

      Thanks for your advice!
    • in this case I define 2 rules
      ['january', <first year>] = something (eg stet or continue)
      ['january'] = palo.data(..palo.eprev..);

      or test boundaries using
      if (!years == palo.first(<database>, 'years'), <default value for first year>, palo.data(...palo.eprev...)))

      I don't know exact palo syntax, I know this from other olap database

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

    • Be very careful with eprev as Palo gived you no way to control the index of an element and eprev relied on the index number.

      A safer way is to create an attribute of the time element "Previous Element" and populate that and ten refer to it via a palo.data to the attribute cube.

      It's a pain to have to maintain it but it's a lot more reliable. You can write a rule with some fancy string manipulation to make it automatic if you feel the need.
      Best wishes

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

      You can check the index using palo.eindex but I've not been able to find a simple way to CHANGE it I'm afraid. Anyone else?

      In TM1 I would maintain the time dimension in a spreadsheet and recompile it. As well as giving me a very easy way to add a year using copy, search and replace, I would also know as a result exactly what the indexing would be.

      As far as I can tell the only way to achieve a similar result in Palo would be:

      * To delete all the elements then copy and paste back in from a spreadsheet ot text document (or another Palo dimension),

      or

      * To process a flat file in a known order with the "clear" parameter set (ptherwiise it just adds new indices at the end.)

      However, Palo's propensity for deleting rules when you delete elements makes both these approaches a bit like playing Russian roulette with your model and is a sure way of getting increasing your stress levels. :P

      There may of course be a way via the API, but that's probably not an option for the average modeller.
      Best wishes

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

      There is a simple way to change the index of an element. Go into the modeler, edit the dimension in question and use the up and down arrows to move the elements. The index is changed to reflect the order of the elements in the dimension. There appears to be no impact on cubes values or rules by such changes.


      Simon
    • Has anyone come up with a better solution yet?
      I understand there are two options:
      • change the index manually with the modeller (and do it for the next few year to save you from trouble)
      • use an extra attribute that holds the previous (or next) month of each month
      The latter solution can be improved by adding an indexing attribute to your months (or even other levels of your time dimension for that matter). I think you need to create your time dimension in a spreadsheet or database table to implement that, but might be worth it.

      Any other ideas?

      trz
    • I have programs (for ALEA in VBA now in Perl) that write a time dimension as textfile. The file is then imported via ETL-Server.

      ETL-Server also has functions to create time dimension(s). I do not know if they sort in desired order as I have not tested this.
    • axi wrote:

      I have programs (for ALEA in VBA now in Perl) that write a time dimension as textfile. The file is then imported via ETL-Server.
      I also have a non-palo solution for that. I created a a database by PDI. The real question is: What do you have in your text-file? :)
      Do you have a 'previous month' attribute, an index attribute, or any other solution to the problem desribed in this thread?

      axi wrote:

      ETL-Server also has functions to create time dimension(s). I do not know if they sort in desired order as I have not tested this.
      No, I just tested it.

      trz
    • the files have the element in the "right" order - since as you oberved in another thread the previous/next elements stem from the order of inserts

      no extra attribute here - it would do no harm, we have it in our datawarehouse for the relational side of things

      just (e.g.)
      2001 2002 ... 2020 200101 200102 ... 200117 200201 ... 200217 200301 ... 202017
      and some other stuff like opening balances, YTD, following in proper order
    • Oh, OK. Now I got it. So your text file is created in a format that can be directly loaded to a dimension.
      I created a relational extract from my time dimension table in Palo-ETL, recreated the tree there, and then loaded the dimension the usual Palo-ETL way.
      Thanks a lot, axi

      trz