# Stock calculation - opening balance / closing balance - how to get correct figures for consolidated

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

• # Stock calculation - opening balance / closing balance - how to get correct figures for consolidated

Hello,

i got to Palo a few months ago and I am really impressed of this software. It is really great and makes a lot of thing more easy. But also a lot of other things more complex to me

I have a cube showing purchases and also sales per month and even per day.

So i have following dimensions:

- time (with date like 01.01.2011 and so on, consolidated to month and afterwards to year)
- products
- measures like purchase/sales/opening balance (aut and man. for correction)/closing balance
- valuetype (liter/pieces/currency)
- datatype (budget/forecast/actual)

For 01.01.2011 I Keyed in the stock values and created a rule which calculates the closing balance out of opening balance + purchase - sales. Afterwards the closing balance is carried forward to the next date by rule.

This really works fine. But I don't get it on the consolidated level (January, February).

Can anybody help me how to get the correct values for opening and closing balance for the consolidated level?

Is there a formula returning the value of the first or last element of a consolidation level? This would help me as to get the value as of for example 31.01.2011 as closing stock on consolidated level January.

Best regards,
Peter
• a tricky topic for a newbie

you might see it from a different angle after you search here for "time dimension"

for my part I have never used rules for stock calculations

I use a time dimension that has a slot for opening balance (at least for each new year) and then has slots for either transactions or closing balances and then claculates from there within the dimension.
• Peter,

one valid way to solve this is to have a dimension like "Movement Types". There you could have elements like "opening stock", "amortisation", "material issue", "closing stock" plus anything you like. Which would probably include your "sales" element (and possibly a few others).

See what I mean? In case you keep having difficulties, do not hesitate to post an example of what you need, I will look into it one of the next evenings.

Cheers
Holger
• Indeed a tricky one. But axi and holger will help you through this ...
You're in good hands.
Robert Tischler
Senior Consultant Vector SW DV GmbH

The post was edited 3 times, last by tish1 ().

• Hello to all of you and thank you for your answers.

@axi
it is really a tricky topic for a newbie But I try my best to solve this as Palo will make my daily business life more easy....hopefully

@holger_b

My time dimension is on a daily basis and January 2011 is consolidated 01.01.2011 up to 31.01.2011.
All my "measures" are included in the measures dimension. So there I have "opening stock", "opening stock manually", "purchase", "sales" and "closing stock".
Opening stock manual is for the reason that I want to override the calucation.

With my rules the calculation already works pretty good for the days. So the 02.01.2011 opening stock is calculated out of closing stock previous element (01.01.2011). Closing stock now= opening balance - purchase - sales.

This really works pretty well for the days.
On the consolidated basis for example January 2011 not. Sum of purchase and sales is correct.
But on consolidated basis I need opening balance of 01.01.2011 and closing balance of 31.01.2011. For every month the first and the last element of the consolidation. And I don't know how to solve.

It is not really important as I can choose the 31.01.2011 in my report and can see the correct value for the closing balance.

I hope it comes clear what I mean. It is hard for me to explain in English.

Best regards and a great weekend to all.

Peter
• Hello Peter,

looks like there is not much to improve here. You could introduce rules to display the opening balance of the first day and the closing balance of the last day, something like this:

['Jan','Opening'] = C:['Jan-01','Opening']
['Jan','Closing'] = C:[Jan-31','Closing']

Does that help? Anything else?

Regards
Holger
• Hello Holger,

thank you very much. I will try.

The only thing is that I will have to inlcude rules for every year as the date element is really 01.01.2011 and the consolidation is January 2011.

But ok This would be worth the effort.

Do you know if there exists a function to get the first and last element of a consolidation level?

Best regards,
Peter
• Yes, try PALO.ECHILD(<db>,<dimension>,<consolidated element>,1) and PALO.ECHILD(<db>,<dimension>,<consolidated element>,PALO.ECHILDCOUNT(<db>,<dimension>,<consolidated element>))

I am not completely sure about the syntax, there is no Palo installed on the machine I am currently using.

Hope this helps
Holger

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

• So holger is on cold turkey now ...
Robert Tischler
Senior Consultant Vector SW DV GmbH
• Absolutely. Eight weeks Infor PM 10, it's like hell
• Hi,

contact me if you need help from a real Alea veteran. . .
Would be nice to see what changed from the good old days.

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

MUCH! not to the better ...

esp. the easy and intuitive user administration
• Hi axi,

do you mean the "Common Object Store"?

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• the "store" is the proprietary technology - I mean the concept and the user interface: how the technology is used

I am always looking for cross platform technology, this store fails badly at this

there was a user revolt about changes in base technology in 2000/2001 during MIS-times, the guy I suppose to be the source of the design decision is now at
thinking-networks.de/TN-Planning.html

his heir is new on the Jedox board

I hoped they learned but I do not think the first one did and I am unsure about the second one ...

at least the design (and license!!!) decisions are made in regard to Palo and it is VERY expensive to change them as the MIS case proved
• Licensing and MIS ...oh my gosh - that reminds me of things I still get a headache from just when thinking about it for a single second. do they still use that sentinel licensing server? ... if yes, ... no, don't think about it. I can already feel the headache come back.
Christian Herzog, Senior Consultant, Vector SW DV GmbH