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

• # RE: Calculate previous element

if you have years sorted in dimension you could use something like this:
['January'] = 0.5 * PALO.DATA(<database>, <cube>, PALO.EPREV(<database>, "years", !years), 'December', ... )
sure it depends on what dimensions you use in your cube
• Sounds good in the first place. I'll try that...

What happens, if there is no previous element?
• 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 ().

• After some problems with eprev it works now. Thanks!

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

• 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,
if this is only for prev year numbers, I prefer this expression:

STR(VALUE(!'Years') - 1,4,0)

It converts the element name into a value, substracts 1 and converts back to 4 char string.

Not 100% sure but it may be "cheaper" than reading an attribute cube.

Holger
• Thanks for the post Pommie - much help indeed. Have run into issues with the indexing of Palo - does anyone have any idea where I can check what the indexes are?
• 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.

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
• Funny how the obvious escapes you sometimes.

Mind you I don't really fancy doing that manually with my time dimension every time I import a new year
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• 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