Group by Property

Hi,

I wonder how you can group-by list properties.

Specifically, as in the attached file, I would like to have figures by properties (category: Fruit/Vegetable) as shown as "Desired Result" Thankyou in advance..

Greg

Groupby_Property_31942.xlsx

Accepted answers

PaulRitner

Hi,

Basically, you are looking to group/aggregate by an attribute.

There are two basic ways to aggregate by an attribute:

The attribute is the parent in the hierarchy. For example, Tomatos roll up to Vegetable.
The attribute is really just an attribute. For example, Products (e.g. tomatos, grapes, etc) are each mapped to a Category (e.g. Vegetable, Fruit). In this case, the only way in Anaplan to aggregate to the group is to create a module (or subsidiary view) that contains Category as a dimension. Then, add a formula in the Sales Line Item of this new module that says something like: ProductSource.Sales[Sum: Products.ProdCategory]

Now, Product Category may be referenced from the Products list itself (i.e. a property in the products list), or sometimes its a (hidden) line item in the Product Sales module. The result is the same either way.

To recap:

We have a module with product sales that contains a line item named "sales", and includes a list named "Product"
We want to summarize sales by Product Category, which is an attribute property maintained in either the Product List or a module that is based on the product list.
We create a module named Category Sales that contains a line item named sales
in 'Category Sales.Sales', we enter a formula that says "product sales.sales[sum: Product.Category]
Note that Product Category must be a list also.

Cheers!

Paul

All comments

PaulRitner

Hi,

Basically, you are looking to group/aggregate by an attribute.

There are two basic ways to aggregate by an attribute:

The attribute is the parent in the hierarchy. For example, Tomatos roll up to Vegetable.
The attribute is really just an attribute. For example, Products (e.g. tomatos, grapes, etc) are each mapped to a Category (e.g. Vegetable, Fruit). In this case, the only way in Anaplan to aggregate to the group is to create a module (or subsidiary view) that contains Category as a dimension. Then, add a formula in the Sales Line Item of this new module that says something like: ProductSource.Sales[Sum: Products.ProdCategory]

To recap:

We have a module with product sales that contains a line item named "sales", and includes a list named "Product"
We want to summarize sales by Product Category, which is an attribute property maintained in either the Product List or a module that is based on the product list.
We create a module named Category Sales that contains a line item named sales
in 'Category Sales.Sales', we enter a formula that says "product sales.sales[sum: Product.Category]
Note that Product Category must be a list also.

Cheers!

Paul

gregoryTan

Thank you, Paul.

I"ll definitely go for option 2 which is exactly what I was looking to.

jlake001

Great fix, such a simple application yet very powerful

Quick Links

Can you help?

Unanswered questions

How to use Client ID and Client Secret for API request directly?
Hi all, The Client ID and Client Secret are available, intended for uploading and downloading files. Could anyone advise how to use these credentials DIRECTLY to make API requests? Thank you!
New UX Filtering for Hierarchy sibling
I have A6 as the parent list and two sibling lists: A6.5 and A7. The A7 list includes an attribute that maps to A6.5. In the UX, the user will select A6.5 using a content selector, and the grid (dimensioned by A7) should display only the A7 items associated with that selected A6.5. Since the selector is a page context and…
A reminder of Bulk Copy - Current year to Prior year version for YoY analysis
A quick reminder of the Bulk Copy functionality. Bulk Copy allows you copy large volumes of data from one slice of a model to another in a single, optimised operation, instead of using formulas or imports. Use case: copy a version (RF1) into a prior year version (PY RF1) using a versions list to allow for year-on-year…