Forum Stats

  • 3,874,643 Users
  • 2,266,757 Discussions
  • 7,911,922 Comments

Discussions

Roll up "Average" value to parent instead of "Adding"

User_VP89I
User_VP89I Member Posts: 183 Blue Ribbon
edited Feb 26, 2018 11:14PM in Planning and Budgeting

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:
User_VP89I

Answers

  • Cord234
    Cord234 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.

  • Liliya B
    Liliya B 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

    User_VP89I
  • User_VP89I
    User_VP89I 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

  • Liliya B
    Liliya B 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

  • Kyle Goodfriend
    Kyle Goodfriend 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

    User_VP89IUser_VP89I
  • Kyle Goodfriend
    Kyle Goodfriend 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
  • User_VP89I
    User_VP89I 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.