This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!

# if this possible...?

April 18 2012 at 9:59 AM
No score for this post
Alan Hutchins

In my accounts dimension, I have a subset that I use to calculate ratios.

In this subset, there are 3 types - selection, value, and proportion

Under each type there are 10 further accounts (so, 1st child of each would be called as follows:

selection = APX (s)
Value = APX (v)
Proportion = APX (p)

I load a 1 against each valid selection by level 0 Entity.
I also load the Revenue value against each level 0 Entity.

There will ALWAYS be the same number of children under each type, and they will always have the same name, differentiated by the suffix.

I calculate the Values, and proportions via hard coded calc scripts. A simplified example is shown below:

Value calculation:

if Entity <> "Missing"

APX (V) = APX (S) * Revenue (thus populating just the relevant entities for this combination, according to the selections)

Proportion calculation:

If the level 0 of subset value <> #Missing

APX (p) = APX (v) / APX (v) -> Total Entity

This works fine, but I wondered whether there was any way I could make it dynamic?

What I would like to do is:

Value:
If Entity <> #Missing THEN
1st Level 0 child of Value subset = 1st Level 0 child of Value subset / Total of Value subset
...and carry on until all Level 0 children of Value subset have been completed

Proportion:
If the level 0 of subset value <> #Missing

1st level 0 child of proportion subset = 1st level 0 child of value subset / Total of Value subset -> Total Entity
...and carry on until all Level 0 children of Proportion subset have been completed

IS THIS POSSIBLE?

My apologies for requesting help on this, but I have been marking time as an Essbase Administrator (had to concentrate on Excel and VBA for a couple of years), so I am a bit rusty to say the least.

 Scoring disabled. You must be logged in to score posts. Respond to this message
Responses

1. Re: if this possible...? - Javier on Apr 19, 2012, 7:56 AM
1. Re: if this possible...? - Anonymous on Apr 19, 2012, 9:41 AM
1. Re: if this possible...? - Javier on Apr 19, 2012, 10:14 AM

2. Re: if this possible...? - Gary Crisci on Apr 19, 2012, 9:56 AM
1. THANKS to both - Alan Hutchins on Apr 19, 2012, 11:00 AM