# Community Version 3.2 issue with cumulative calculations

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

• # Community Version 3.2 issue with cumulative calculations

I have the following rule in a simplified test cube - just this one rule.

Weeks 111201 and 112202 are indexed 1 and 2 respectively in my
Time dimension.

It tests whether the time element is index 1 in the dimension in which case it makes cumulative sales for that week = Sales, otherwise it make Cumulative Sales = Cumulative Sales last week (index -1 for based on current week) + Sales this week. All pretty basic stuff.

### Source Code

1. ['Cumulative Sales'] = N:
2. IF(
3. PALO.EINDEX("OSImpact","Time",!'time') == 1,
4. ['Sales'],
5. PALO.DATA("OSImpact","Test","Cumulative Sales",PALO.ENAME("OSImpact","Time",PALO.EINDEX("OSImpact","Time",!'Time') - 1)) + ['Sales']
6. )

Leaving aside the apparently insoluble B: / N: issue this is a rule similar to rules I have used time and time again in both TM1 and Palo.

When I view the results of Sales and Cumulative Sales for weeks 111201
and 112202 which are indexed 1 and 2 respectively in my Time dimension it is causing the server to go into a loop, use up all the memory and hang.

Has anyone else experienced anything similar or am doing something stupid here?

Just for fun I wrote it in an easier to read, more "palo", style

### Source Code

1. ['Cumulative Sales'] = N:
2. IF(
3. (!'Time') == PALO.EFIRST("OSImpact","Time"),
4. ['Sales'],
5. PALO.DATA("OSImpact","Test","Cumulative Sales",PALO.EPREV("OSImpact","Time",!'Time')) + ['Sales']
6. )

Same problem
Best wishes

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

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

• Hi,

I'd allways go for creating cumulations through hirarchies in time dimensions...

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• Leaving aside the question of why the calculations that used to work don't any more, you'd need an ETL to do cumulatives in a dimension structure in a way that kept you sane, and that isn't available in the CE.

It CAN be done that way but the point of using a rule is that it is self maintaining and thus reduces the margin for admin error when time periods are added.

Edit - the other reason for using the rule is that I want to calculate cumulatives for a limited number of data variables, and display them under the same time period as the other single week data.

In other words I want to be able to show "Sales" & "Sales Cumulative" / Week 1 and not Sales / Week 1 and Sales / Week 1 Cum which would make UI design unnecessarily complicated
Best wishes

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

The post was edited 1 time, last by Pommie: Additional information added ().

• Hi Pommie,

### Pommie wrote:

you'd need an ETL to do cumulatives in a dimension structure in a way that kept you sane, and that isn't available in the CE

Fortunately you are wrong here. You can use ETL Server in Palo Suite (fka CE).

### Pommie wrote:

it is self maintaining and thus reduces the margin for admin error when time periods are added

That can be done through ETL by adding only one calendar extract to your existing time dimension definition.

### Pommie wrote:

why the calculations that used to work don't any more

If you want, you can send me your example and I'll have a look at it, but you may try that rule in Jedox Moalp Server (fka PE).
I'm also a little sceptical about the performance of this rule. ...

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

Thanks for the reply

Fortunately you are wrong here. You can use ETL Server in Palo Suite (fka CE).
Ah yes - my fault - I downloaded the suite, ran setup.exe and was surprised not to see any install of the ETL. I have worked out now that setup.exe is just the Palo for excel set up isn't it I can't find the 3.2 CE manual and the 3.3 manual is obviously rather different, and I found installing from memory to be a challenge!

I have now uninstalled the Excel add in and then reinstalled it and the rest of the suite. Without a manual I am having fun getting the web / ETL stuff to run. If anyone can point me to a manual that explains it all for 3.2 CE I would be most grateful.

That can be done through ETL by adding only one calendar extract to your existing time dimension definition.
Can you be more specific? I have to confess to being uncomfortable with things that rely on maintenance routines being executed. As with TM1 I am sure Palo's dimension consolidations are faster than rule based calculations but generally performance was quite acceptable in my model (and that was without markers). As with most design issues there is a trade off between maintainability / usability and efficiency.

I would prefer to stay with a self maintaining solution that gives me the ability to view my cumulatives at the same time as my week's data under a single time element unless there was a pressing reason to do it another way.

As regards testing the cumulative calculation error you just need to set up a test cube with 2 dimensions Time (Weeks) and Measures (Sales & Cumulative Sales)

Then write a rule like this:

['Cumulative Sales'] = B:IF((!'Time') == PALO.EFIRST("OSImpact","Time"),['Sales'],PALO.DATA("OSImpact","Test","Cumulative Sales",PALO.EPREV("OSImpact","Time",!'Time')) + ['Sales'])

(This Sends the server into a spin)

or this

['Cumulative Sales'] = N:IF(PALO.EINDEX("OSImpact","Time",!'Time') == 1,['Sales'],PALO.DATA("OSImpact","Test","Cumulative Sales",PALO.ENAME("Test","Time",PALO.EINDEX("Test","Time",!'Time') - 1)) + ['Sales'])

(This was causing a server crash but now just displays 0s in all Cumulative Sales)

Trying to "Drill Palo Rule" on a cell containing Cumulative sales gives the message that "There's no rule for this cell"

These are rewritten rules but I am 99.9999% sure that that they are identical to those I had working in 3.1 over 18 months ago.
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• Hello Pommie,

### Pommie wrote:

Without a manual I am having fun getting the web / ETL stuff to run. If anyone can point me to a manual that explains it all for 3.2 CE I would be most grateful.

Here you can download the manual:
palo.net/index.php?id=8

Regards.
Djordja Markovic

Interessant things:
Internal derby:
jedox.com/community/palo-forum…ad&postID=14338#post14338
Calculate your cube size:
jedox.com/community/palo-forum…ad&postID=14406#post14406
• Thanks Djordja

All I can see on that page s the "What's new" PDF document and the software download links. I can't see a manual. Am I missing something?
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• ### Pommie wrote:

Thanks Djordja

All I can see on that page s the "What's new" PDF document and the software download links. I can't see a manual. Am I missing something?

Hi Pommie,

you are right. They change the sites

Here is the correct link:
jedox.com/de/jedox-downloads/j…kumentationen-archiv.html

Regards.
Djordja Markovic

Interessant things:
Internal derby:
jedox.com/community/palo-forum…ad&postID=14338#post14338
Calculate your cube size:
jedox.com/community/palo-forum…ad&postID=14406#post14406
• Thanks again Djordja

Unfortunately my German isn't up to translating a technical manual. I rarely get past ordering a beer before start to reach the limits of my German vocabulary

The equivalent link from the sitemap to jedox.com/en/jedox-downloads/j…cumentations-archive.html gives a 404 error I am afraid so i am still stuck.
Best wishes

John Hobson
The Planning Factory, Lytham, UK
www.planfact.co.uk
• ### Pommie wrote:

Thanks again Djordja

Unfortunately my German isn't up to translating a technical manual. I rarely get past ordering a beer before start to reach the limits of my German vocabulary

The equivalent link from the sitemap to jedox.com/en/jedox-downloads/…ns-archive.html gives a 404 error I am afraid so i am still stuck.

Hello Pommie,

I think we have it now:
jedox.com/en/jedox-downloads/j…ocumentation-archive.html

ps: just go to the "www.jedox.com > english > download > Jedox Documentation Archive" and there are the documentations

Regards.
Djordja Markovic

Interessant things:
Internal derby:
jedox.com/community/palo-forum…ad&postID=14338#post14338
Calculate your cube size:
jedox.com/community/palo-forum…ad&postID=14406#post14406
• And thanks again Djordja
Best wishes

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

### tish1 wrote:

That can be done through ETL by adding only one calendar extract to your existing time dimension definition.
Could you please show me how to do this, or point me to the right piece of documentation or example?

If we take for example importRelDb.xml from the ETL Client Samples, there are two measures there, NetValue and Units (normalized into a "Measure" dimension) and a "Year" calendar dimension. How would you add a third measure to that example, called Cumulative, with the cumulative sum of the NetValue for all previous years? (the first year can just be itself) And what about a Difference measure, showing the difference from the previous year? (here, the first year could be zero)

Here is what I'm talking about:

[IMG:http://img38.imageshack.us/img38/4263/screenshot20120427at114.png]

These would be integration and differentiation, in a mathematical sense, except that here it would be done over the discrete intervals of a given dimension, Years in this case.
• Hi,

the values "cumulative" column can be created by using the "time-to-date" function in the "Calendar" extract.
And a little more complex, but also based on a "Calendar" extract, you can create a hierarchy in your date dimension, that'll show you the difference between two cumulative years by using the cumulative elements created in the first step. But this can also be done with rules. As you have cumulative values now in your cube, the rules can be quite simple.
You very often need that in financial models, when dealing with multiple currencies.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH