Decreasing the length of your formulas

[#1]

All too often, when analyzing a model, we see very long, convoluted, and complex formulas. These are commonly nested IF THEN ELSE statements and can be in excess of 5,000 characters.

Line item subsets (LISS) can be used to shorten the length of the formula while making the solution more dynamic, scalable, maintainable—and will, in turn, increase the health of the model, as well as the overall performance.

Problem statement:我需要总结客户，产品和方案的三个不同源模块的订单项，但是当版本更改时，我需要订单项来为客户和产品提供适当的信息。

Model design

Lists:

• Transactions—list of all transactions
• Company Flat—list of all companies
• Product Flat—list of all products
• Scenarios—list of scenarios, very easily could have used the native versions
• Metrics—list of the metrics needing to be reported (units, price, net amount)

Modules:

• Transaction01 Actual Data: loaded from the source system

• Transaction02计划数据：从源系统加载

• Transaction03 Budget Data: loaded from the source system

Let’s take a look at a way that is possible—but is hard to maintain, performs poorly, and contradicts many Planual rules.

Solution #1:

创建一个由客户平面，产品平面，场景，指标和时间的模块尺寸化。我使用的指标列表（包括单位，价格和净额），因为我只想为最终解决方案定义一个订单项。

行项目解决方案的公式＃1：

IF ITEM(Scenarios) = Scenarios.Actuals THEN IF ITEM(Metrics) = Metrics.Units THEN 'TRANS01 Actual Data'.'Units - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Price THEN 'TRANS01 Actual Data'.'Price - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Net Amount THEN 'TRANS01 Actual Data'.'Net Amount - Actual'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE IF ITEM(Scenarios) = Scenarios.Plan THEN IF ITEM(Metrics) = Metrics.Units THEN 'TRANS02 Plan Data'.'Units - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Price THEN 'TRANS02 Plan Data'.'Price - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Net Amount THEN 'TRANS02 Plan Data'.'Net Amount - Plan'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE IF ITEM(Scenarios) = Scenarios.Budget THEN IF ITEM(Metrics) = Metrics.Units THEN 'TRANS03 Budget Data'.'Units - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Price THEN 'TRANS03 Budget Data'.'Price - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF ITEM(Metrics) = Metrics.Net Amount THEN 'TRANS03 Budget Data'.'Net Amount - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE 0

The good news is, this works. But it violates so many best practices, yet we see this all the time. This is relatively tame compared to what we normally see in that this is only 1,535 characters (with spaces), while we routinely see upwards of 4,000 to 7,000 character formulas.

Let’s look at what is wrong with the formula:

Hard coding（平面规则：2.02-12 - 没有硬编码以列出成员）

该公式包含对不同列表（方案和指标）的硬编码引用。如果创建新方案或创建新的度量标准怎么办？模型构建器将必须记住与上述类似的每个订单项并进行更新。同样，如果需要删除这些列表成员之一，目前是不可能的，因为这些成员已被硬编码为该行项目。此外，如果需要将这些列表（方案和指标）标记为生产数据列表（对于ALM），那么这是不可能的，您将获得以下内容：错误：

To get around the hardcoding, you could use SYS modules with Booleans.

This does remove the hardcoding and makes it a bit more dynamic, but the formula is still not efficient:

如果sys方案属性。那么，如果系统指标属性属性。然后'trans01实际数据'。“单位 - 实际” [sum：sys trans properies.com.pany，sum：sys trans properties.product.product.productions] else，如果sys量级属性。然后'trans01实际数据'。“价格 - 实际” [sum：sys trans properties com.pany，sum：sys trans properties.product.product.product] else如果sys量表属性.net金额？然后'trans01实际数据'。'净额 - 实际'[sum：sys trans properties.com.pany，sum：sys trans properties.product.product.product] else 0如果sys scenarios properties.plan.plan？那么，如果系统指标属性属性。然后“ Trans02计划数据”。“单位 - 计划'[sum：sys trans properties.com.pany，sum：sys trans properties.product.product.product] else，如果SYS Metrics properties.price.price？然后“ Trans02计划数据”。“价格 - 计划'[sum：sys trans properties.com.pany，sum：sys trans properties.product.product.profuct] else，如果sys Metrics properties.net金额？然后“ Trans02计划数据”。“净额 - 计划'[sum：sys trans properties.com propany，sum：sys trans properties.product.product]]其他0如果sys scenarios properties.budget.budget？那么，如果系统指标属性属性。 THEN 'TRANS03 Budget Data'.'Units - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Price? THEN 'TRANS03 Budget Data'.'Price - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE IF SYS Metrics Properties.Net Amount? THEN 'TRANS03 Budget Data'.'Net Amount - Budget'[SUM: SYS Trans Properties.Company, SUM: SYS Trans Properties.Product] ELSE 0 ELSE 0

You have to remember, even though this formula is defined only once for the line item, it is getting calculated on the number of cells that line item contains. So, if this line item contains 1 million cells, this formula is getting processed 1 million times.

Maintenance

This was touched on a bit earlier, but there is no way this can be easily maintained, easily understood by new model builders, nor is it scalable. When you are creating the logic for these line items, you have to remind yourself you may not just be designing this for you to maintain; you may pass on to either the client or to another model builder and want this process to be as seamless as possible.

Poor performance

Planual Rules: 1.07-02 Version Line Item subsets

2.02-01 Nested Ifs

公式中的2.02-02 <12表达式

2.02-03 No Repeated expressions

2.02-18 Break up formulas

Long nested IF THEN ELSE statements are bad for performance because this formula is now single-threaded. This means it will traverse the formula until it gets a “hit”, in this case where the Scenario and Metric combination match. Remember, it is also better to break up the formulas into multiple line items versus squeezing the logic into one giant line item.

Another aspect of performance is the number of functions in a formula. Look at all of the SUM’s in this formula (nine of them), yet they’re almost identical with the only differences being the line item and source of the data. It would be much more efficient if a calculation module was created to do the sums separately, but how do you do that with different source modules and different line items? This is where a line item subset comes into play and makes life much easier.

The solution:

Let’s take a look at how we can solve this problem in a better way by consolidating our source data, creating a dynamic way to align our metrics, and bringing it all together with a better performing formula. As you can see below, the data flows from left to right without traversing back to previous modules.

步骤1-行项目子集

Create a line item subset (LISS) using the three source modules and check all of the metrics.

If you are not familiar with how to create a line item subset seehere.

Step 2 - Collect statement

• 创建一个模块dimensionalized的行项目ubset you just created and the lists of the source modules of the line item subset—in this case, Transactions.
• Create a new line item, number formatted, with the formula being COLLECT(). This collect statement essentially gets the data for all of the line items and turns the line items into a “dynamic” list. Also, since this is a “staging” module, you will be able to change Summary option to None.

步骤3-计算模块

• Create a calculation module to sum the data from the different source modules to the desired dimensions—in this case, Customer Flat, Product Flat, the line item subset (to get the “dynamic” line items), and Time.

为什么这个这么重要？还记得原始解决方案中的九种总和吗？好吧，我们将它们减少到一个只有一个，但是我们得到了完全相同的结果。

Step 4 - Mapping module

• 创建一个“映射”模块，该模块将指标（列表）与版本列表相一致，将订单项目格式化为“行”项子集。

What is great with this solution is it is 100% dynamic in that we can map the relevant “metrics” to versions without changing the formula. If we don’t want to calculate these metrics for any combination, then just remove or blank out the values.

Step 5 - The results

• 在最初的解决方案模块,dimensionalized by Customer Flat, Product Flat, and Time, create a new line item named Solution 2, formatted as number.
• For the formula, simply type 'CALC01: Sum of Metrics'.Data[LOOKUP: 'Map01: Metrics to Scenarios'.Mapping]

Basically, we are doing a lookup of the values that we summed in step 3 (the single summation formula) based on the mapping values in step 4.

步骤6 -添加调整或一个不同的来源

Now, let’s say the use case changed, and now we want to add adjustments to the result, but only for the Plan version. If we had the original solution with the big IF THEN ELSE statement, we would have to reengineer to incorporate this new source for only the Plan version. With the new solution, we can simply create a new line item subset, enter the values into the mapping module for the correct version, and then update the final formula.

• Adjustment module:Same dimensionality as the results (Customer Flat, Product Flat, and Time).

• Create a new line item subset using the above module as the source.

• Create a “collect” module for this data with the summaries turned off.

• Create a new line item in the Mapping module, but this time format it to the line item subset you just created for Adjustments.

Notice, we only entered line items for the Plan, but we could do this for the other versions as well.

• In our reporting module, you can either create a new line item or continue with Solution 2 and type this in for your formula: 'CALC01: Sum of Metrics'.Data[LOOKUP: 'Map01: Metrics to Scenrios'.Mapping] + 'COLC02: Collection of Adjustments'.Data[LOOKUP: 'Map01: Metrics to Scenarios'.Adjustment Mapping]

As you can see, the data for the Plan version is different, but when we change the version to Actual or to Budget, the numbers will be the same because we are not adding the adjustments in for those versions (in the mapping module, those line items were blank).

Conclusion:By creating an additional module or two, as well as employing a line item subset, you can greatly increase the overall health and performance of your model while also making it much easier to scale and maintain. For more information on Line Item Subsets, please go to this article (Line Item Subsets Demystified）。

[einas.ibrahim]

Hey@rob_marshall

I got a headache just reading through the long formulas, I can't imagine you trying to deconstruct it. Very impressive work.
I have seen formulas that span 3 pages on a word document (sorry didn't count the characters) and what I usually notice is that the design of the data modules is not correct or optimum which leads to the need to use nested IFs.
The issue I am usually faced with is...
Do I try to fix the formula as much as I can while still depending on the faulty data modules design?
OR
Solve the problem from the root and redesign the data modules?
I am sure you can imagine what consequences will be there due to redesigning the data modules since they are used by other calculations modules -at least.

[ROB_MARSHALL]

@einas.ibrahim

That is always a tough call to make. What I was really hoping to convey in this article is you can do LISS on multiple modules and include multiple sources in your results based off of different mappings. That is the key to this! The mapping module doesn't have to be just on Actuals or Budget, but can be on entirely different source modules all done with the correct mapping.

[安德鲁蒂]

@rob_marshall- 如果将三个源模块加载到带有列表版本的一个模块尺寸中，您是否会得到相同的结果？那么，您无需进行多次重新贴印。但是我得到了您要传达的问题。

[ROB_MARSHALL]

@andrewtye

You should always try to not use actions and instead use formulas as actions can be potentially blocking actions for other users (Planual 5.01-03).

Additionally, using formulas is more dynamic, and if done correctly, performs much better, and the results are immediate instead of having to wait on a process to be run.

[戴维史密斯]

To the above point, remember Anaplan (and the Hyperblock) is designed for data entry by users and then the DAG (Directed Acyclic Graph) kicks in. Think pinpoint rifle shot rather than shotgun!

We often see actions used as a crutch for lack of modelling understanding. Much better to model if necessary; way more efficient

David

[einas.ibrahim]

@rob_marshall

您传达了完美使用Liss的观点。这个解决方案很棒。
Although the main issue to address was the long formula, I appreciate the flexibility the LISS and mapping modules brings even more.

I have used a combination of LISS and mapping modules to solve similar - yet at a smaller scale - issues before. Your methodical thinking and the way you expanded on the use of LISS takes this to a new level.

谢谢，阅读您的文章后，我已经感到更聪明，请让他们来。

[PhilippErkinger]

如果其他陈述在差异报告中，我仍然会出现大规模的领域之一。有趣的是，这几乎在所有FP＆A用例中都会发生。简单的映射 /总和函数通常由于每行的不同计算方法或最终用户想要看到显示为％的值而行不通。

如果模块可以处理几个订单项子集，那将是很棒的。意思是集合（）顶部的collect（）。在Anaplan的早期，这是可能的。希望它能有一天回来。在此之前，我始终建议在Anaplan构建此类繁琐的逻辑之前先首先将Excel加载项用于方差报告。

[戴维史密斯]

@PhilippErkinger

In my first implementation I "accidentally" created a module with 2 line item subsets

Problem was it ended up crashing the model when I used a formula summary method

活到老，学到老！

David

[CallumW]

This is a fantastic article, thank you@rob_marshall呢当将来与客户交谈时，肯定会引用本文。

[PhilippErkinger]

I didn't say that it worked well, but it was possible!Rumour has it that there are still models out there that have two line item subsets.

[ROB_MARSHALL]

@callumw

Thanks, glad you liked it.