Changing the Sign for Aggregation

Use Case:

In a lot of financial account hierarchies, costs are shown as positive numbers—but need to be subtracted from the totals.  With Anaplan, this is easily achievable with line items.

Picture 1.png

The line item formulae are as follows:

  • Margin = Sales – COGS
  • Profit = Margin – Costs

This is a very simple example that is easily solved.  However, often there are additional calculations that need to be performed on the data—meaning the financial accounts are represented by a list.

Picture 2.png

As lists work on straight aggregation, the same data from above does not calculate in the way we require.

Picture 3.png


It requires the use of two additional line items and the use of the ratio summary method.

1. Create a P&L Signage module.

  • Dimension this by the P&L list.
  • Add line items to hold the signage.

In the example below, I have used a Boolean to signify which lines should be “flipped”, although you could just use the signage line item and manually enter the values.Picture 4.png2. In the P&L Module:

a. Create two additional line items

  • Temp
  • Ratio

Note: The ratio line item only needs to be dimensioned by the P&L list, so this is likely to be a subsidiary view.

b. Add the following formulae.

  • Temp = Value * Ratio
  • Ratio = 'SYS10 P&L Signage'.Signage Calc

3. Amend the summary option for the Value line item to Ratio as follows:

Picture 5.png

4. Amend the summary option for the Ratio line item to Max.

This last setting is the key to getting the totals to work correctly.Picture 6.png

This will also work if the costs are “credits”, so it should be shown as negative values.

Picture 7.png

 It also works if all the costs within a subtotal are credits.

Picture 8.png

The final example is if you have a time dimension in the module (which is very likely needed).

Picture 9.png

If you are using a line item subset as a dimension of a module, this technique, using ratio, is particularly relevant because line item subsets require the totals to be simple aggregation in the same was as a list does.

To achieve the same result, follow the steps above, but your modules will have the line item subset as the dimension instead of the P&L list.

Picture 10.png

The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.

Brilliant stuff David. You never cease to amaze! This piece of functionality will allow users to keep the reporting signage, but be able to have a proper aggregating report

Yeah, I wish this had been documented (or I would have come up with this solution) three years ago when I had to bang my head against the wall and add a ton of temporary line items to get this type of reporting implemented for a client of mine.

Latest Articles
11 hours ago
Organizing List Subsets
Best Practices
UX Roles: Overview
Best Practices
Labels (1)