Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Resolving Formula Collision and Working with Cells

Objective

After completing this lesson, you will be able to resolve formula collision and work with cells

Formula Collision

Formula collision occurs when a query has two structures and both structures contain formulas. The collision occurs at the point where the formulas intersect.

Eliminate Formula Collision

Always check if the default behavior delivers the desired result. If it does not then the following options are available in the Formula Collision Resolution field on the General tab of the properties of a formula (calculated key figure at query level):

Use Result of This Formula
The result of this formula has priority in a collision.
Use Result of Concurrent Formula
The result of a competing formula has priority in a collision.
If two colliding formulas are set to the same setting, the last specification made has priority.
On the Priorities tab, you also can view and modify the priority handling by explicitly specifying which property is actually taken into account.

Note

You can also resolve formula collision by defining a formula in the intersection cell where the formula collision occurs. See Working with Cells.

Working with Cells

Cells are at the intersection of two structures in a query definition. Cells may be formulas, selections, references, or help cells. In this context, cells are not the same as cells in Microsoft Excel.

When you define selection conditions and formulas for a query with two structures, cell definitions are automatically created to determine the cell values.

Cell Editor in SAP BW Query Designer in SAP HANA Studio

Using the Cell Editor in SAP BW Query Designer in SAP HANA Studio, you can:

Use specific cells as a Reference Cells so that they can be referenced in other calculations.
Define Formula Cells and Selection Cells to overwrite the initial cell definition; they can also be used to avoid or to resolve formula collision.
Define Help Cells. They have no direct relationship to the structures. They are not displayed in the report. They can be formulas or selections and can be used in Formula Cells or further Help Cells.

Cell Editor

Create cell-specific formulas and conditions for detailed queries. To open the Cell Editor in BW Query Designer in SAP HANA Studio, choose the Cells tab. The functions New Reference Cell, New Selection Cell, and New Formula Cell are available.

Additionally, you can define as manyHelp Cells as you like.

Resolve Formula Collision and work with Cells

Launch the following demonstration to explore a business scenario where formula collision occurs and has to be resolved and the definition of different types of cells is needed.

DemoStart Demo

Continue to quiz