How do I sum across dimensions other than time?

PrevContributor
PrevContributor
in Modeling

Can't see how this was done in the allocations video. I have a list for products, and a line item for sales. I want to introduce a line item that returns the total sales for all products against every product. Beyond allocation use, is there a way to introduce a line item that returns the value at the next level up a multi-level hierarchy? - Assume we have a three level product hierarchy: Product>Product Group>Total Products. Such a function would enable me to return the % contribution that each Product contributes to its Product Group, and the contribution that each Product Group contributes to Total Products.

Tagged:

Best Answers

  • PrevContributor
    PrevContributor
    Answer ✓
    Answering my own question (first part) -

    Use the SELECT function.

    This is very, very powerful. Makes it ridiculously simple to do things that are a right PITA to do using IBM/Cognos Planning.

    In my example, I wanted to summarise by category the total sales for products, and the following did exactly what I was looking for -
    =Sales by category.Sales[LOOKUP: Category, SELECT: Products.Total Products]
  • PrevContributor
    PrevContributor
    Answer ✓
    [quote=Gerald Donovan]Beyond allocation use, is there a way to introduce a line item that returns the value at the next level up a multi-level hierarchy?[/quote]
    Hi Gerald,

    You can do this as long as Product and Product Group are two separate lists, and the Product Group list is set as the parent hierarchy of the Product list. If this is already set up, then the one thing you will need to add to the Product list is a list-formatted property (formatted as Product Group list). Once added, go to the Grid View and you can copy over the Parent column into your new property to populate the appropriate Product Groups.

    Finally, the formula for your Line Item returning the next level up in the multi-level hierarchy is: Sales[LOOKUP: Product.Product Group]

    Hope that helps. I would love to hear any other solutions to this as well.

Answers

  • PrevContributor
    PrevContributor
    [quote]

    Finally, the formula for your Line Item returning the next level up
    in the multi-level hierarchy is: Sales[LOOKUP: Product.Product Group]
    [/quote]Thanks for the method and formula, solved a problem I was trying to work through, I had a quick go with other options - one I would hope could work was utilising PARENT(x) function instead of havingto  reference the list property, but this does not  seem to  work (save on the extra property column in the list)

    Tell me if you find a way... cheers

Categories