## Forum Stats

• 3,874,643 Users
• 2,266,757 Discussions

Discussions

#### Categories

Member Posts: 183 Blue Ribbon
edited Feb 26, 2018 11:14PM

Hi Gurus,

I have a requirement to roll up average across dimensions. For "Period" dimension we do that using the "Time Balance" property, but how can we achieve that for other dimensions.

For Eg: Selling Price, at parent levels of Entity, Product etc should be average of the child/L0 values.

Thanks,

~RN

Tagged:

• Member Posts: 171 Blue Ribbon
edited Feb 20, 2018 8:08AM

Is the account Stored?  If so, after you run your aggregations, recalculate that account at parent levels.  Generally you can do this by using a UDA and tagging all your parent level members, then fixing on those with the UDA and recalcling the account.

• Member Posts: 90 Blue Ribbon
edited Feb 20, 2018 2:52PM

Depends on the complexity of your outline, but in general having dynamic member that will back calculate the average would help you.

Example #1: if you are looking for weighted avg Selling Price, you could add a dynamic account that should have a formula as 'Sales\$'/'Units'

Example #2: if you just want an average of the input values you can have a stored member that will have 1 for every lowest level combination that you would later consider for your average (for example an account like  'Number of product lines' that will have 1 for every product and entity that have non missing sales units) and create a dynamic calculation that will be:  'Selling Price' (the data in the input account)  / 'Number of product lines'.

In both examples you will have to aggregate your outline to get the average.Just name the input accounts as xxxxxx_Input and use them for data entry only, use the Dynamic accounts for Reports and Ad Hoc.

Hope it helps

• Member Posts: 183 Blue Ribbon
edited Feb 21, 2018 12:58PM

Thanks for your input, having one will be an overhead. Will Count() function help?

For now we have parked this requirement, but I would like to explore the possibilities.

Thanks,

RN

• Member Posts: 90 Blue Ribbon
edited Feb 21, 2018 1:28PM

Hi,

Any solution will have pros and cons. Dynamic Count() might work if your outline is small. I think it would be too heavy for reporting.

Solution as was advised by @Cord234 would work as well, but this too have a cons that every time you need to aggregate your outline you would need to recalculate parent members.

Try various and see what works better for your application.

Hope it helps

• Member Posts: 185
edited Feb 22, 2018 6:48AM

I am not sure I totally understand the issue but if you are trully trying to take the average of the level 0 members one thin I have done is as follows.

• Assume the member in question is called member1
• Create a stat member that is stored and equal to 1 if the member1 has data - in a calc on form save for example, and this member is named counterstat

Formula would be something like

working(

if(member1+0 != 0)

Counterstat=0;

else

counterstat=#missing;

endif)

• have another stat member that that is set to dyn calc and two pass is true, and has a formula of member1 / counterstat;

this would assume you are trying to take an average and excluding missing and 0

• Member Posts: 185
edited Feb 22, 2018 6:50AM

Obviously the member names are up to you. I normally have a member called

• Member1_total
• member1_count
• member1 would be what the user sees and is the dyn two pass calc member
• Member Posts: 183 Blue Ribbon
edited Feb 26, 2018 11:14PM

Hi Kyle,

Thanks for your inputs, I will try this. Just want to confirm one thing. Should that be Counterstat=1; instead of Counterstat=0;  ?

working(

if(member1+0 != 0)

Counterstat=1;

else

counterstat=#missing;

endif)

This discussion has been closed.