# Rule to calculate value of previous year

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

• # Rule to calculate value of previous year

Hi,

I would like to set up a rule to calculate the value of the previous year.

At the moment I use: ['Abweichung'] = [Umsatz','Jahr':offset(0)] -['Umsatz','Jahr':offset(-1)]

It seems to work but its to slow and can not be use. Are their better ways to do this?

Regards,
Robert
• Hi,

if I'd need the deviation on a yearly basis I'd use ETL to create the relevant rules. I'd include markers there too.

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

For the deviation, I have solved it in a similar way. Rules didn't work (cube not responding after minutes). Therefore I do a cube extract and transform the year in a post cube load (add) job.

This solution is okay, but it is not possible for more complex calculation like deviation in percentage. I face the same problem as bevor, I can not understand that rules are usable. A simple rule like [Profit]/[Profit last year]-1 and the report is extremly slow.

I think deviation in percentage is a standard meassure for all business reports. I'm interested in how somebody else solved this problem. Until now I solved it with extra formulas in excel or Jedox Web but then sorting is not really possible.

thx & regards,

robert
• Hi,

yes this rule with offset function is not fully optimized.
here are scenarios when the rule can be slow
1. export of large areas calculated by the rule - solution is to reduce area size
2. element 'Abweichung' has some parent element that is being calculated in the view. solution: define rule for parent elements that will override default consolidation ['parent_of_Abweichung'] = ['Abweichung'] + other_siblings_of_Abweichung
3. there is a B: rule in your cube that uses ['Abweichung'] as source - eliminate such combination of rules or apply #1 and #2 on such rule

Jiri
• Hi Jiri,

I do not use other rules, therefore it must be the first scenario. How could I reduce the area size?

At the moment I try to reduce the dimensions of the cube, according to reports I use different cubes (with a different number of dimensions). Thats good for speed - but naturally bad for administration
21 dimensions (some with thousands of items) seems to be to much for jedox (or may be I have to change some configuration - I don't know until now?).

regards,

Robert
• You have not specified how you calculate the data.
1. Excel sheet - palo.data or palo.datac - in this case it is probably problem #2 - element 'Abweichung' has some parent - see my answer from May 24th
2. using cube export with rule calculated cells or using of etl cube extract - in this case you could try to reduce extracted/exported area specifying less elements in the extracted/exported area

It is usually rule performance killer mistake to put everything into one cube with many dimensions. You have to fight always problems like this. Then it is very easy to generate query on data that makes no sense and takes year to calculate.

regards
Jiri