Line item subsets are one of the most powerful and efficient features in Anaplan, yet one of the least understood. The COLLECT() function is probably the only “black box” function within Anaplan as is it not immediately apparent what it is doing or where the source values are coming from. In the following article, I will cover how to understand line item subsets more easily, and also explain their many uses, some of which do not need COLLECT() at all.
For more information on creating line item subsets see Line Item Subsets in Anapedia.
A line-item subset is a list of items drawn from one or more line items from one or more modules. Put simply, it converts line items into a list on which calculations can be performed. There are some restrictions:
Line item subsets can be used for many different areas of functionality. For the examples used, I have based them on the final model from the new Level 1 training. Download the model and follow the instructions to practice on the same structures.
These examples are deliberately simplified, but I hope you find these insightful and easy to transfer into your models to simplify the formulae and provide more flexibility to your users.
Table of Contents:
This is the classic use of line item subsets. A source module contains line items, and subsequently, you need to perform additional calculations on these line items. While in some cases this can be managed through complex formulae, normally these workarounds break most of the best practice guidelines and should be avoided.
Use Case example:
The source module contains forecast data with line items for the profit and loss lines in U.S. dollars. We need to convert these values into local currency based on the Country dimension.
The source modules are as follows:
The first step is to create the line item subset, and for this report, we only want summary values.
Now that the line item subset has been created, it is available to be used in a module.
Note that the values are the same as those in REP03 and the line items are now shown in list format (no formatting). Also note that these values are from the Forecast version, as the target module does not have versions, so the Current Version is used as the source automatically.
Note that the Exchange Rate line item should be set as a Subsidiary view (excluding the line item subset from the applies to) because we are showing it on the report for clarity. If this display was not required, the calculation could be combined with the Local Currency formula.
You can also use a line item subset to help with the transformation between source and target modules.
Use Case example:
We want to summarize costs (from the reporting P&L) into Central and Locally controlled costs.
We use the SUM formula because the source dimension and the mapping dimension are the same. So, “If the source is the same, it’s a SUM.”
Line item subsets can contain line items from multiple modules. There is a caveat though; all modules must share at least one common dimension/hierarchy and/or have a Top Level set for non-matching dimensions.
Use case example:
Based on user-entered settings, we want to compare the values from two time periods for metrics from three different modules and calculate the absolute and % variances.
The source modules all share a common dimension:
Note: G3 Location has a G2 Country as a parent
The module for the user parameters is:
And the metrics required are:
We could solve this problem without using a line item subset:
The formula for Month 1 is:
I won’t repeat the formula for Month 2, as it is effectively the same, just referencing the Month 2 line item in the source.
You can see, that even for a small set of metrics, this is a large complex formula, going against best practices. So, let’s not do that.
Note, this time we are using LOOKUP rather than SUM because the source dimension doesn’t match the dimension of the mapping module.
I think you’ll agree that the formula is much easier to read and it is more efficient.
However, we can do even better; but note that there now are two ‘lookups’ in the formula. The more “transformations” there are in the formulae, the more work the engine needs to do. We can remove one of these by changing the target module dimensionality.
Note, only one lookup is needed in the formula.
Another use case that line item subsets can be used for is filtering. And this functionality has nothing to do with staging data or mapping modules. It is possible to filter line items and these can also be filtered based on other dimensions too.
Use Case example:
Based on user-entered settings, for the reporting module (REP03) we want to show different line items for each year and version.
We already have set up the Years to Versions filter module
We now want to set up the user-driven parameters. To ensure that the users’ settings do not affect each other, we need to use the system generated Users’ list.
Note that Employee expenses and Other Costs are not available to check. This is because, in REP03, they are a simple aggregation and are shown as Parents of the other line items.
So, how do we resolve this? You can “trick” the model by turning these setting off.
The subtotals are now available to check in the filter module.
It is worth noting, be careful when doing this. If you are using the line item subsets as a dimension in a data entry module, the totals will not calculate correctly. See Final Thoughts for more details.
To set up the filter
The module will now filter line items and years when the version page selector is changed.
Note the subtotals work correctly in this module because it is not data entry.
Line item subsets can be used in conjunction with Dynamic Cell Access to provide very fine-grained control over data; again, without any mapping modules or COLLECT() statements
Use Case Example:
In the following module
The following rules apply:
To set up the access:
The module now looks like this:
I mentioned at the outset that you can lose formatting when using a line item subset. However, in some cases, it is possible to keep formatting along with calculations
Use Case Example:
Using the values from REP03, we want to classify Sales and Costs and then calculate a cost % to Sales. Yes, we could do this in the module itself as a separate line item, but we also want to be able to reclassify the source line items from a dashboard using mappings rather than change the blueprint formula. We also want to maintain formatting.
For this example, I have just changed the styles to illustrate the point
Note the formatting is preserved.
Finally, I want to mention a piece of functionality that is not well known but very powerful; Version Formula. Utilizing line item subsets in conjunction with versions, Version Formula extends the ‘Formula scope” functionality. It is possible to control formulae using Formula Scope, but there are limited options.
Use Case example:
Let’s assume that we have actuals data in one module, the budget data in another and we want to enable the forecast to be writeable. The current version (in the versions setting) is set to Forecast
For this example, there is only one line item in the target module, but this functionality allows the flexibility to set different rules per version for each line item
Note that now at the top, you can see that there is a Version Formula set.
We mentioned the aggregation behavior and the ‘Is Summary’ setting earlier. Let me show you how this and the construction of the formulas affect the behavior of the line item subset
We will use the following module as an example. This module is only used to set up the line item subset, so no dimensions are needed.
Note that the subtotal formulae are simple aggregations.
This means the subtotal lines:
The following module is dimensioned by the line item subset to highlight 1. and 2. above.
If we decide we don’t want the Employee costs in the line item subset, two things happen:
To mitigate the latter point there are two remedies.
Note the 'Parent' and 'Is Summary' settings, the Costs subtotal now calculates correctly.
If we change the formulae to be something other than simple addition, you will see that calculation is fine in the source module,
but not in the line item subset module.
Why is this?
Remember the 'Is Summary' setting we changed in the Filters section when we adjusted the formula the 'Is Summary' is now unchecked
This means that the line item subset doesn’t treat the line as a calculation, hence the data entry 0 shown instead.
If your costs need to be positive (as in this example), it is possible to calculate correctly using a ratio formula. This works for normal line items/lists as well as line item subsets. See Changing the sign for Aggregation for more details