*Content reviewed for accuracy June 2020
Making sure that production data lists are correctly marked within a model is a key step to setting up and using Application Lifecycle Management (ALM) . This guide will provide a solution to how someone can make revisions to their model to allow for the tagging of a list as a production data list. Please note: this solution doesn’t work if there are hard-coded references on non-composite summary items. For more information on working with production lists and ragged hierarchies, please visit Production lists and ragged hierarchies logic.
The issue arises as a model administrator needs to tag a production data list, but there are hard-coded references in the model that won’t allow the person to do so. When this occurs and the model administrator tries to tag it as a production list, they will get a warning similar to this:
See Formula Protection for more details.
To fix this issue, all direct formula references to production data lists need to be changed to be indirect references to lists using either LOOKUPs or Boolean formatted conditional logic. Below, you will find a step-by-step guide to replacing these formulas.
Identify formulas with hard-coded references
There is now an easy way to identify all of the formulas which are hard-coded to production data lists.
Check the 'Referenced in Formula' column in the General Lists section.
This will show the line items where the list is used. Check the respective formula for hard-coded references. If there are no hard-coded references, then it is OK to check the list as a production data list. This is the recommended approach, as just setting the lists without prior checking may lead to a rollback error being generated, which could be time-consuming for large models (as well as frustrating).
It is possible to just export the General Lists grid to help where there are multiple references for the same list and then use formulas and filters to identify all offenders in the same effort. This option will save significant amounts of time if there are many line items that would need to be changed.
You are looking for direct references on the list members:
[SELECT: List Name.list member]
ITEM(List Name) =List Name.List member
The following constructs are valid, but not recommended, as any changes to the names or codes could change the result of calculations:
IF CODE(ITEM(List Name))=
IF NAME(ITEM(List Name))=
After following those steps, you should have a list of all of the line items that need to be changed in the model in order for production data list to be open to being checked. Please note: There may still be list properties that have hard-coded references to items. You will need to take note of these as well, but as per D.I.S.C.O., (Best practice for Module design) we recommend that List Properties are replaced with Line Items in System Modules.
Replacing model formulas:
The next step is to replace these formulas within the model. For this, there are two recommended options.
The first option (Option 1 below) is to replace your SELECT statements with a LOOKUP formula that is referencing a list drop-down. Use this option when there are 1:1 mappings between list items and your formula logic. For example, if you were building out a P&L variance report and needed to select from a specific revenue account, you might use this option.
The second option (Option 2 below) for replacing these formulas is to build a logic module that allows you to use Booleans to select list items and reference these Boolean fields in your formulas. Use this option when there is more complex modeling logic than a 1:1 mapping. For example, you might use this option if you are building a variance report by region and you have different logic for all items under Region 1 (ex: budget – actual) than the items under Region 2 (ex: budget – forecast).
(Option 1) Add List Selections module to be used in LOOKUPs for 1:1 mappings:
From here you should make a module called List Selections, with no lists applied to it and a line item for each list item reference that you previously used in the formulas that will be changed. Each of these line items will be formatted as the list that you are selecting to be production data.
Afterward, you should have a module that looks similar to this: An easy and effective way to stay organized is to partition and group your line items of similar list formats into the same sections with a section header line item formatted as No Data and a style of "Heading 1."
After the line items have been created, the model administrator should use the list drop-downs to select the appropriate items which are being referenced. As new line items are created in a standard mode model, the model administrator will need to open the deployed model downstream to reselect or copy and paste the list formatted values in this module since this is considered production data.
Remove hard-coding and replace with LOOKUPs:
Once you have created the List Selections module with all of the correct line items, you will begin replacing old formulas, which you’ve identified in Excel, with new references.
For formulas where there is a SELECT statement, you will replace the entire SELECT section of the formula with a LOOKUP to the correct line item in the list selections.
Old Formula = Full PL.Amount[SELECT: Accounts.Product Sales] New Formula = Full PL.Amount[LOOKUP: List Selections.Select Product Sales]
For formulas where there is an IF ITEM (List Name) = List Name Item, you will replace the second section of the formula after the ‘=’ to directly reference the correct line item in the list selections.
Old Formula = If ITEM(Accounts) = Accounts.Product Sales THEN Full PL.Amount ELSE 0 New Formula = IF ITEM(Accounts) = List Selections.Select Product Sales THEN Full PL.Amount ELSE 0
(Option 2) Modeling for complex logic and many to many relationship:
In the event that you are building more complex modeling logic in your model, you should start by building Boolean references that you can use in your formulas. To accomplish this, you will create a new module with Boolean line items for each logic type that you need. Sticking with the same example as above, if you need to build a variance report where you have different logic depending on the region, start by creating a module by region that has different line items for each different logic that you need similar to the view below:
Once you have the Boolean module set up, you can then change your hard-coded formulas to reference these Boolean formatted line items to write your logic. The formula may look similar to this:
IF Region Logic.Logic 1 THEN logic1 ELSE IF Region Logic.Logic 2 THEN logic2 ELSE IF Region Logic.Logic 3 THEN logic3 ELSE 0
Here is a screenshot of what the end result may look like:
This method can be used across many different use cases and will provide a more efficient way of writing complex formulas while avoiding hard-coding for production data lists.
Selecting production data list:
After all of the hard-coded formulas have been changed in the model, you can navigate back to the Settings tab, and open General Lists. In the Production Data column, check the box for the list that you want to set as a production data list.
Repeat for each list in the model that needs to be a production data list:
For each list in the model that you need to make a production data list, you can repeat the steps throughout this process to successfully remove all hard-coded list references.
Details of known issues
Performance issues with long nested formulas
Need to have a long formula on time as a result of nested intermediate calculations.
If the model size does not prevent from adding extra line items, it's a better practice to create multiple intermediate line items and reduce the size of the formula, as opposed to nesting all intermediate calculations into one gigantic formula.
This applies to summary formulae (SUM, LOOKUP, SELECT).
Combining SUM and LOOKUP in the same line item formula can cause performance issues in some cases. If you have noticed a drop in performance after adding a combined SUM and LOOKUP to a single line item, then split it into two line items.
RANKCUMULATE causes slowness
A current issue with the RANKCUMULATE formula can mean that the time to open the model, including rollback, can be up to five times slower than they should be.
There is currently no suitable workaround. Our recommendations are to stay within the constraints defined in Anapedia.
SUM/LOOKUP with large cell count
Separate formulas into different line items to reduce calculation time (fewer cells need to recalculate parts of a formula that would only affect a subset of the data).
A known issue with SUM/LOOKUP combinations within a formula can lead to slow model open and calculation times, particularly if the line item has a large cell count.
All line items do not apply to time or versions.
Y = X[SUM: R, LOOKUP: R]
Y Applies to [A,B]
X Applies to [A,B]
R Applies to [B] list formatted [C]
Add a new line item 'intermediate' that must have 'Applies To' set to the 'Format' of 'R'
intermediate = X[SUM: R]
Y = intermediate[LOOKUP: R]
This issue is currently being worked on by Development and a fix will be available in a future release
Calculations are over non-common dimensions
Anaplan calculates quicker if calculations are over common dimensions. Again, best seen in an example. If you have, List W, X Y = A + B Y Applies To W, X A Applies To W B Applies To W This performs slower than, Y = Intermediate Intermediate = A + B Intermediate Applies To W All other dimensions are the same as above. Similarly, you can substitute A & B above for a formula, e.g. SUM/LOOKUP calculations.
Cell history truncated
Currently, history generation has a time limit of 60 seconds set. The history generation is split into three stages with 1/3 of time allocated to each.
The first stage is to build a list of columns required for the grid. This involves reading all the history. If this takes more than 20 seconds, then the user receives the message "history truncated after x seconds - please modify the date range," where X is how many seconds it took. No history is generated.
If the first stage completes within 20 seconds, it goes on to generate the full list of history.
In the grid only the first 1000 rows are displayed; the user must Export history to get a full history. This can take significant time depending on volume.
The same steps are taken for model and cell history. The cell history is generated from loading the entire model history and searching through the history for the relevant cell information. When the model history gets too large then it is currently truncated to prevent performance issues. Unfortunately, this can make it impossible to retrieve the cell history that is needed.
Make it real time when needed
Do not make it real time unless it needs to be.
By this we mean, do not have line items where users input data being referenced by other line items unless they have to be. A way around this could be to have users have their data input sections, which is not referenced anywhere, or as little as possible, and, say, at the end of the day when no users are in the model, run an import which would update into cells where calculations are then done. This may not always be possible if the end user needs to see resulting calculations from his inputs, but if you can limit these to just do the calculations that he needs to see and use imports during quiet times then this will still help.
We see this often when not all reporting modules need to be recalculated real time. In many cases, many of these modules are good to be calculated the day after.
Don't have line items that are dependent on other line items unnecessarily.This can cause Anaplan to not utilize the maximum number of calculations it can do at once. This happens where a line items formula cannot be calculated because it is waiting on results of other line items. A basic example of this can be seen with line item's A, B, and C having the formulas: A - no formula B= A C = B Here B would be calculated, and then C would be calculated after this. Whereas if the setup was: A - no formula B = A C = A Here B and C can be calculated at the same time. This also helps if line item B is not needed it can then be removed, further reducing the number of calculations and the size of the model. This needs to considered on a case-by-case basis and is a tradeoff between duplicating calculations and utilizing as many threads as possible. If line item B was referenced by a few other line items, it may indeed be quicker to have this line item.
Summary cells often take processing time even if they are not actually recalculated because they must check all the lower level cells.
Reduce summaries to ‘None’ wherever possible. This not only reduces aggregations, but also the size of the model.
Assume the following Non-Composite list, ragged hierarchy, needs to be set to Production Data.
We need to refer to the ultimate parent to define the logic calculation. In the example, we have assumed that children of Parent 1 and Parent 3 need to return the 'logic 1' value from the constants module below, and those under Parent 2 return 'logic 2,' and we apportion the results based on the initial data of the children.
Data / IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 1' THEN Data[SELECT: 'Non-Composite List'.'Parent 1'] ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 2' THEN Data[SELECT: 'Non-Composite List'.'Parent 2'] ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 3' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Child 3.1' THEN Data[SELECT: 'Non-Composite List'.'Parent 3'] ELSE 0
Select Proportion * IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 1' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 3' OR PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Child 3.1' THEN Parent Logic Constants.'Logic 1' ELSE IF PARENT(ITEM('Non-Composite List')) = 'Non-Composite List'.'Parent 2' THEN Parent Logic Constants.'Logic 2' ELSE 0
These “hard references” will prevent the list from being set as a production list.
Create a Parents Only list (this could be imported from the Non-Composite list). As we don't need the sub-level parents, we do not need to include 'Child 3.1,' even though it is technically a parent.
To calculate the proportion calculation without the SELECT, a couple of intermediate modules are needed:
Parent Mapping Module
This module maps the Non-Composite parents to the Parents Only list. Due to the different levels in the hierarchy, we need to check for sub levels and use the parent of Child 3.1. In this example, the mapping is automatic because the items in the Parents Only list have the same name as those in the Non-Composite list. The mapping could be a manual entry if needed.
The formula and “applies to” are:
Non-Composite Parent: PARENT(ITEM('Non-Composite List')) Applies to: Non-Composite List
Parent of Non-Composite Parent: PARENT(Non-Composite Parent) Applies to: Non-Composite List
Parent to Map: IF ISNOTBLANK(PARENT(Parent of Non Composite Parent)) THEN Parent of Non Composite Parent ELSE Non Composite Parent Applies to: Non-Composite List
Parents Only List FINDITEM(Parents Only List, NAME(Parent to Map)) Applies to: Parents Only List
Parents Only Subtotals
An intermediary module is needed to hold the subtotals.
Parent Logic Calc.Data[SUM: Parent Mapping.Parents Only List]
Parent Logic? Module
We now define the logic for the parents in a separate module.
Add Boolean line items for each of the “logic” types.
Then you can refer to the logic above in the calculations.
Data / Parents Only Subtotals.Calculation[LOOKUP: Parent Mapping.Parents Only List]
Lookup Proportion * IF Parent Logic?.'Logic 1?'[LOOKUP: Parent Mapping.Parents Only List] THEN Parent Logic Constants.'Logic 1' ELSE IF Parent Logic?.'Logic 2?'[LOOKUP: Parent Mapping.Parents Only List] THEN Parent Logic Constants.'Logic 2' ELSE 0
The list can now be set as a production list as there are no “hard references”. Also, the formulas are smaller, simpler and now more flexible should the logic need to change. If Parent 3 needs to use Logic 2, it is a simple change to the checkbox.
Reducing the number of calculations will lead to quicker calculations and improve performance. However, this doesn’t mean combining all your calculations into fewer line items, as breaking calculations into smaller parts has major benefits for performance.
Learn more about this in the Formula Structure article.
How is it possible to reduce the number of calculations? Here are three easy methods:
Turn off unnecessary Summary method calculations.
Avoid formula repetition by creating modules to hold formulas that are used multiple times.
Ensure that you are not including more dimensions than necessary in your calculations.
Turn off Summary method calculations
Model builders often include summaries in a model without fully thinking through if they are necessary. In many cases, the summaries can be eliminated. Before we get to how to eliminate them, let’s recap on how the Anaplan engine calculates.
In the following example we have a Sales Volume line-item that varies by the following hierarchies:
This means that from the detail values at SKU, City, and Channel level, Anaplan calculates and holds all 23 of the aggregate combinations shown below—24 blocks in total.
With the Summary options set to Sum, when a detailed item is amended (represented in the grey block), all the other aggregations in the hierarchies are also re-calculated. Selecting the None summary option means that no calculations happen when the detail item changes. The varying levels of hierarchies are quite often only there to ease navigation, and the roll-up calculations are not actually needed, so there may be a number of redundant calculations being performed. The native summing of Anaplan is a faster option, but if all the levels are not needed it might be better to turn off the summary calculations and use a SUM formula instead.
For example, from the structure above, let’s assume that we have a detailed calculation for SKU, City, and Channel (SALES06.Final Volume). Let’s also assume we need a summary report by Region and Product, and we have a module (REP01) and a line item (Volume) dimensioned as such.
REP01.Volume = SALES06 Volume Calculation.Final Volume is replaced with REP01.Volume = SALES06.Final Volume[SUM:H01 SKU Details.Product, SUM:H02 City Details.Region]
The second formula replaces the native summing in Anaplan with only the required calculations in the hierarchy.
How do you know if you need the summary calculations? Look for the following:
Is the calculation or module user-facing?
If it is presented on a dashboard, then it is likely that the summaries will be needed. However, look at the dashboard views used. A summary module is often included on a dashboard with a detail module below; Effectively, the hierarchy sub-totals are shown in the summary module, so the detail module doesn’t need the sum or all the summary calculations.
Detail to Detail
Is the line item referenced by another detailed calculation line item? This is very common, and if the line item is referenced by another detailed calculation the summary option is usually not required. Check the Referenced by column and see if there is anything referencing the line item.
Calculation and staging modules
If you have used the D.I.S.C.O. module design, you should have calculation/staging modules. These are often not user-facing and have many detailed calculations included in them. They also often contain large cell counts, which will be reduced if the summary options are turned off.
Can you have different summaries for time and lists?
The default option for Time Summaries is to be the same as the lists. You may only need the totals for hierarchies, or just for the timescales. Again, look at the downstream formulas.
The best practice advice is to turn off the summaries when you create a line item, particularly if the line item is within a Calculation module (from the D.I.S.C.O. design principles).
Avoid Formula Repetition
An optimal model will only perform a specific calculation once. Repeating the same formula expression multiple times will mean that the calculation is performed multiple times. Model builders often repeat formulas related to time and hierarchies. To avoid this, refer to the module design principles (D.I.S.C.O.) and hold all the relevant calculations in a logical place. Then, if you need the calculation, you will know where to find it, rather than add another line item in several modules to perform the same calculation.
If a formula construct always starts with the same condition evaluation, evaluate it once and then refer to the result in the construct. This is especially true where the condition refers to a single dimension but is part of a line item that goes across multiple dimension intersections. A good example of this can be seen in the example below:
START() <= CURRENTPERIODSTART() appears five times and similarly START() > CURRENTPERIODSTART() appears twice.
To correct this, include these time-related formulas in their own module and then refer to them as needed in your modules.
Remember, calculate once; reference many times!
Taking a closer look at our example, not only is the condition evaluation repeated, but the dimensionality of the line items is also more than required. The calculation only changes by the day, as per the diagram below:
But the Applies To here also contains Organization, Hour Scale, and Call Center Type.
Because the formula expression is contained within the line item formula, for each day the following calculations are also being performed:
And, as above, it is repeated in many other line items.
Sometimes model builders use the same expression multiple times within the same line item. To reduce this overcalculation, reference the expression from a more appropriate module; for example, Days of Week (dimensioned solely by day) which was shown above. The blueprint is shown below, and you can see that the two different formula expressions are now contained in two line items and will only be calculated by day; the other dimensions that are not relevant are not calculated.
Substitute the expression by referencing the line items shown above.
In this example, making these changes to the remaining lines in this module reduces the calculation cell count from 1.5 million to 1500.
Check the Applies to for your formulas, and if there are extra dimensions, remove the formula and place it in a different module with the appropriate dimensionality .
Thinking through the results of a modeling decision is a key part of ensuring good model performance—in other words, making sure the calculation engine isn’t overtaxed. This article highlights some ideas for how to lessen the load on the calculation engine.
Formulas should be simple; a formula that is nested, or uses multiple combinations, uses valuable processing time. Writing a long, involved formula makes the engine work hard. Seconds count when the user is staring at the screen. Simple is better. Breaking up formulas and using other options helps keep processing speeds fast.
You must keep a balance when using these techniques in your models, so the guidance is as follows:
Break up the most commonly changed formula
Break up the most complex formula
Break up any formula you can’t explain the purpose of in one sentence
Formulas with many calculated components
The structure of a formula can have a significant bearing on the amount of calculation that happens when inputs in the model are changed. Consider the following example of a calculation for the Total Profit in an application. There are five elements that make up the calculation: Product Sales, Service Sales, Cost of Goods Sold (COGS), Operating Expenditure (Op EX), and Rent and Utilities. Each of the different elements is calculated in a separate module. A reporting module pulls the results together into the Total Profit line item, which is calculated using the formula shown below. What happens when one of the components of COGS changes? Since all the source components are included in the formula, when anything within any of the components changes, this formula is recalculated. If there are a significant number of component expressions, this can put a larger overhead on the calculation engine than is necessary.
There is a simple way to structure the module to lessen the demand on the calculation engine. You can separate the input lines in the reporting module by creating a line item for each of the components and adding the Total Profit formula as a separate line item. This way, changes to the source data only cause the relevant line item to recalculate.
For example, a change in the Product Sales calculation only affects the Product Sales and the Total Profit line items in the Reporting module; Services Sales, Op EX, COGS and Rent & Utilities are unchanged. Similarly, a change in COGS only affects COGS and Total Profit in the Reporting module.
Keep the general guidelines in mind. It is not practical to have every downstream formula broken out into individual line items.
Plan to provide early exits from formulas
Conditional formulas (IF/THEN) present a challenge for the model builder in terms of what is the optimal construction for the formula, without making it overly complicated and difficult to read or understand. The basic principle is to avoid making the calculation engine do more work than necessary. Try to set up the formula to finish the calculations as soon as possible.
Always put first the condition that is most likely to occur. That way the calculation engine can quit the processing of the expression at the earliest opportunity.
Here is an example that evaluates Seasonal Marketing Promotions:
The summer promotion runs for three months and the winter promotion for two months.
There are more months when there is no promotion, so this formula is not optimal and will take longer to calculate.
This is better, as the formula will exit after the first condition more frequently.
There is an even better way to do this. Following the principles from above, add another line item for no promotion.
And then the formula can become:
This is even better because the calculation for No Promo has already been calculated, and Summer Promo occurs more frequently than Winter Promo.
It is not always clear which condition will occur more frequently than others, but here are a few more examples of how to optimize formulas:
FINDITEM formula The Finditem element of a formula will work its way through the whole list looking for the text item, and if it does not find the referenced text, it will return blank. If the referenced text is blank, it will also return a blank. Inserting a conditional expression at the beginning of the formula keeps the calculation engine from being overtaxed.
IF ISNOTBLANK(TEXT) THEN FINDITEM(LIST,TEXT) ELSE BLANK
IF BLANK(TEXT) THEN BLANK ELSE FINDITEM(LIST,TEXT)
Use the first expression if most of the referenced text contains data and the second expression if there are more blanks than data.
LAG, OFFSET, POST, etc. If in some situations there is no need to lag or offset data, for example, if the lag or offset parameter is 0. The value of the calculation is the same as the period in question. Adding a conditional at the beginning of the formula will help eliminate unnecessary calculations:
IF lag_parameter = 0 THEN 0 ELSE LAG(Lineitem, lag_parameter, 0)
IF lag_parameter <> 0 THEN LAG(Lineitem, lag_parameter, 0) ELSE 0
The use of formula a or b will depend on the most likely occurrence of 0s in the lag parameter.
Booleans Avoid adding unnecessary clutter for line items formatted as BOOLEANS. There is no need to include the TRUE or FALSE expression, as the condition will evaluate to TRUE or FALSE.
IF Sales > 0 then TRUE ELSE FALSE
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 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.
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:
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.
Now that the line item subset has been created, it is available to be used in a module.
Create a module with the following dimensions:
LIS: REP03 Currency
Add the following line items:
In the Base Currency line item, enter the formula: COLLECT()
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.
Add the following formulae to the remaining line items to complete the calculation.
Exchange Rate = 'DATA02 Exchange Rates'.Rate[LOOKUP: 'SYS03 Country Details'.Currency Code]
Local Currency = Base Currency / Exchange Rate
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.
Create a list (Controllable Costs) containing two members.
Create a line item subset (as before) using just REP03 as the source module.
Create a staging module with the following dimensions:
LIS: REP03 Cost Reporting
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.
Map the lines as applicable.
Create a reporting module with the following dimensions.
Add a line item called Costs.
Add the formula: 'REP07 Cost Reporting Staging'.Data[SUM: 'SYS14 Cost Mapping'.Mapping]
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.”
Multiple Source Modules
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:
REV03 Margin Calculation: G2 Countries, P2 Products, Month
EMP03 Employee Expenses by Country: G2 Countries, Month
OTH01 Non-Employee Expenses: G3 Location, E1 Departments, Month
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:
Create a list (Reporting Metrics) containing the list items above.
Create a module with the following dimensions.
The formula for Month 1 is:
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.
For multi-module line item subsets, it is best practice to use Multi> to represent the various modules.
Open the line item subset and chose the three modules.
Create a staging module (this is best practice following the DISCO principle), with the following dimensions.
LIS: Multi>Variance Reporting
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 Reporting Metrics.
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.
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.
Copy the reporting module from above.
Remove the formulae for Month 1 and Month 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.
Month 1 = 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 1']
Month 2 = 'REP05 Variance Report Staging'.Data[LOOKUP: 'SYS11 Time Variance Reporting'.'Month 2']
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.
Create a line item subset based on REP03
Select all line items
Create a new module with the following dimensions:
LIS: REP03 Filters
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.
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
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
Use Case Example:
In the following module
The following rules apply:
Bonus % is set by the central team so it needs to be read only.
All metrics for Exec are not allowed to be edited.
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.
To set up the access:
Create a line item subset based on EMP01
Select all line items
Create an Access Driver module with the following dimensions:
LIS: EMP01 DCA
Add two Boolean formatted line items
Enter the values as below
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
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
Create a line item subset based on REP03.
Create a staging module with the following dimensions:
LIS: REP03 Cost%
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]
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
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.
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:
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.
If we decide we don’t want the Employee costs in the line item subset, two things happen:
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.
To mitigate the latter point there are two remedies.
Include the subtotals and hide them – The lines are still calculating and taking space.
If possible, adjust the formula structure.
Remove the subtotals formula.
Add in the Costs formula as to use the detailed items; no intermediate totals.
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,
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