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行项目子集in Anapedia.
行项目子集是从一个或多个模块中从一个或多个订单项中绘制的项目列表。简而言之，它将行项目转换为可以执行计算的列表。有一些限制：
- Line item subsets can only contain numeric formatted line items.
- Only one line item subset can be used as a dimension in a module.
- Although line items can contain formulas, the items in a line item subset can only aggregate to a simple subtotal.
- Styles on the line items are not transferred over to the line item subset.
行项目子集可用于许多不同的功能领域。对于使用的示例，我将它们基于新的1级培训的最终模型。下载模型并按照说明在相同的结构上进行练习。
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:
- Calculations on Calculations
- Transformation
- Multiple Source Modules
- Filters
- Dynamic Cell Access
- Line Items Subsets with Line Item Subsets
- 版本公式
- Final Thoughts
- 其他资源亚搏娱乐电子
Calculations on Calculations
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.
- In the settings tab, choose Line Item Subsets and click insert.
- We recommend prefixing with LIS: the name of the module and simple description.
- Clicking on the Line Item Subset header item (in settings) will display the Line Item Subsets screen.
- Click on the newly created line item subset and the … and select the module(s) required; in this case, it is REP03.
- Select which line items you wish to include in the line item subset.
现在已经创建了行项目子集，可以在模块中使用。
- Create a module with the following dimensions:
- LIS: REP03 Currency
- G2 Country
- Time (Years)
- Add the following line items:
- Base Currency
- 汇率
- Local Currency
- In the Base Currency line item, enter the formula: COLLECT()
请注意，这些值与REP03中的值相同，现在以列表格式（无格式）显示了行项目。另请注意，这些值来自预测版本，因为目标模块没有版本，因此当前版本被用作源。
- Add the following formulae to the remaining line items to complete the calculation.
- 汇率= 'DATA02 Exchange Rates'.Rate[LOOKUP: 'SYS03 Country Details'.Currency Code]
- Local Currency = Base Currency / Exchange Rate
行项目应该注意汇率t 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.
Transformation
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.
- 创建一个包含两个成员的列表（可控成本）。
- Central
- Local
- Create a line item subset (as before) using just REP03 as the source module.
- 创建一个具有以下维度的登台模块：
- LIS: REP03 Cost Reporting
- G2 Country
- Time (Years)
- Add a line item (Data) and enter COLLECT() as the formula.
- Set the Summary method to None; we do not need subtotals in this module.
- Create a mapping module, dimensioned by LIS: REP03 Cost Reporting.
- Add a line item (Mapping) formatted as the Controllable Costs list.
- 将行映射为适用。
- Create a reporting module with the following dimensions.
- 可控成本
- G2 Country
- Time (Years)
- 添加一个称为成本的订单项。
- Add the formula: 'REP07 Cost Reporting Staging'.Data[SUM: 'SYS14 Cost Mapping'.Mapping]
我们使用总和公式是因为源维数和映射维度相同。所以，“If the source is the same, it’s a SUM.”
Multiple Source Modules
行项目子集可以包含来自多个模块的订单项。虽然有一个警告。所有模块必须至少共享一个通用维度/层次结构和/或具有用于非匹配维度的顶级设置。
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.
源模块都共享一个共同的维度：
- Rev03保证金计算：G2国家，P2产品，月份
- EMP03按国家按国家 /地区的员工支出：G2国家，月份
- OTH01非雇员费用:G3位置,E1 Departments, Month
注意：G3位置作为父母的G2国家
The module for the user parameters is:
And the metrics required are:
- Margin
- 薪水
- Bonus
- Rent
- Utilities
We could solve this problem without using a line item subset:
- 创建一个包含上述列表项的列表（报告指标）。
- Create a module with the following dimensions.
- 报告指标
- G2 Country
- Users
第1个月的公式是：
- IF ITEM(Reporting Metrics) = Reporting Metrics.Margin THEN 'REV03 Margin Calculation'.Margin[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Salary THEN 'EMP03 Employee Expenses by Country'.Salary[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Bonus THEN 'EMP03 Employee Expenses by Country'.Bonus[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Rent THEN 'OTH01 Non Employee Expenses'.Rent[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE IF ITEM(Reporting Metrics) = Reporting Metrics.Utilities THEN 'OTH01 Non Employee Expenses'.Utilities[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1'] ELSE 0
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.
- Create the line item subset as before.
- 多模块排列项子集,pra是最好的ctice to use Multi> to represent the various modules.
- Open the line item subset and chose the three modules.
- 创建一个分期模块（这是按照迪斯科原理的最佳实践），并具有以下维度。
- LIS: Multi>Variance Reporting
- G2 Country
- Time (Months)
- Add a line item (Data) and enter COLLECT() as the formula.
- Set the Summary method to None; we do not need subtotals in this module.
- 创建一个通过报告指标来尺寸的映射模块。
- Add a line item formatted LIS: Multi>Variance Reporting.
- Map the lines accordingly.
- In the reporting module from above, change the Month 1 and Month 2 line item formulae to.
- 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1', LOOKUP: 'SYS12a Reporting Metrics Mapping'.Mapping]
- 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 2', LOOKUP: 'SYS12a Reporting Metrics Mapping'.Mapping]
Note, this time we are using LOOKUP rather than SUM because the source dimension doesn’t match the dimension of the mapping module.
我认为您会同意该公式更容易阅读，并且更有效。
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.
- Copy the reporting module from above.
- 删除第1个月和第2个月的公式。
- Replace Reporting Metrics with LIS: Multi>Variance Reporting as the dimension (applies to).
- Add the following formulae for Month 1 and Month 2 respectively.
- 月1 ='rep05差异报告staging'.data [查找：'sys11时间差异报告'。'月1']
- Month 2 = 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 2']
Note, only one lookup is needed in the formula.
Filters
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.
- 基于REP03创建订单项子集
- Select all line items
- Create a new module with the following dimensions:
- LIS: REP03 Filters
- Users
- Versions
- Add a single line item (Show?) formatted as a Boolean
- Enter values as you wish
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.
那么，我们如何解决这个问题？您可以通过关闭这些设置来“欺骗”模型。
The subtotals are now available to check in the filter module.
It is worth noting,be carefulwhen doing this. If you are using the line item subsets as a dimension in adata entrymodule, thetotals will not calculate correctly.有关更多细节，请参见最终想法。
To set up the filter
- In REP03, set the following filters
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.
Dynamic Cell Access
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
用例示例：
In the following module
The following rules apply:
- Bonus % is set by the central team so it needs to be read only.
- 不允许所有执行指标进行编辑。
- Car Allowances are not applicable for Production.
- Phone Allowances are not applicable for Production, Finance or HR, and the allowances for Sales should be read only.
设置访问：
- Create a line item subset based on EMP01
- Select all line items
- Create an Access Driver module with the following dimensions:
- LIS: EMP01 DCA
- G2 Country
- E1 Departments
- 添加两个布尔格式的订单项目
- Read?
- 写？
- 输入以下值
- Now in EMP01 assign the Read Access and Write Access drivers to the module
The module now looks like this:
Line Items Subsets with Line Item Subsets
我一开始提到，使用订单项子集时，您可能会失去格式化。但是，在某些情况下，可以继续格式化以及计算
用例示例：
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
- 基于REP03创建订单项子集.
- 创建一个具有以下维度的登台模块：
- LIS: REP03 Cost%
- G2 Country
- Time (Years)
- Add a line item call Data and enter COLLECT() as the formula and set the Summary method to None.
- Create a second line item subset based on REP10 (the target module).
- Create a mapping module dimensioned by the LIS: REP03 Cost%
- Create a line item formatted as LIS: REP10
- Map the lines accordingly
- In the target module set following formula for both Sales and Costs line items (Yes, it is the same formula for both!)
- 'REP09 LISS vs LISS - Staging'.Data[SUM: 'SYS20 Cost% Mapping'.Mapping]
注意格式保留。
版本公式
最后，我想提一下的功能，该功能尚不为人所知，但非常强大。版本公式。将订单项子集与版本结合使用，版本公式扩展了“公式范围”功能。可以使用公式范围来控制公式，但是选项有限。
Use Case example:
假设我们在一个模块中拥有实际数据，另一个模块中的预算数据，我们希望使预测能够写入。当前版本（在版本设置中）设置为预测
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
- Create a line item subset based on the above and select the line item(s).
- Now in the blueprint view of the target module click Edit>Add Version Formula.
- Now choose the Version to which the formula applies.
- You will now see a different formula bar at the top of the blueprint view.
- Enter the following formula:
- 'DATA01 P&L Actuals & Budget'.Revenue
- Repeat the above for Budget with the following formula:
- 'REV03 Margin Calculation'.Revenue
Note that now at the top, you can see that there is a Version Formula set.
Final Thoughts
我们提到了汇总行为和“摘要”设置。让我向您展示这和公式的构建如何影响订单项子集的行为
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:
- Calculate correctly when used as a dimension in a module.
- Are not available for data entry.
The following module is dimensioned by the line item subset to highlight 1. and 2. above.
如果我们决定不希望员工在行项目子集中的费用，则会发生两件事：
- The indentation changes for the detailed cost lines because they are now not part of a parent hierarchy on display.
- The Costs subtotal doesn’t calculate. This is because the Costs subtotal needs the intermediate subtotals to exist within the line item subset.
为了减轻后者remedie有两个点s.
- 包括小计并隐藏它们 - 线仍在计算和占用空间。
- If possible, adjust the formula structure.
- Remove the subtotals formula.
- 添加使用详细项目的成本公式；没有中间总数。
- Re-add the subtotal formulas.
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,
但不在“行”子集模块中。
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. SeeChanging the sign for Aggregationfor more details
