Have you ever created a formula that repeats another formula within a cell? Did you know that this repetition may be slowing down your reports?
For example, you may have a report where you want to pull IBM Planning Analytics data and replace zeros with the word “None.” You can write this as a formula via:
| =IF ( DBRW(cube,dim1,dim2,dim3)=0, “None”, DBRW(cube,dim1,dim2,dim3) ) |
Within this formula, the IF check first looks at the DBRW formula to determine a value. If the value is not zero then the IF check needs to re-run the exact same DBRW formula to produce the value that will appear in the cell. This means that one cell potentially needs to run the same formula twice! While this doesn’t sound like a big deal, it can easily impact the performance of your report if you have many iterations of the formula.
Excel has introduced a new function called LET that will help optimize this situation. This function allows you to define a variable that can repeatedly be used within a calculation. It will help you improve performance since it will only execute the calculation once within the cell.
The syntax of the function is:
| =LET (VarName, VarValue, Calculation) |
- VarName is the name of the variable to be used
- VarValue is the value of the variable
- This can be a hard coded number or it can be a formula
- Calculation is a formula that uses the variable name
- This can be a simple formula such as X+Y or it can use any existing Excel function
A simple iteration of this formula can be written as:
| =LET (x, 5, x*2) |
This formula would result in the number 10.
The LET formula also allows you to define multiple variables. A simple example of multiple variables can be written as:
| =LET (x, 5, y, 2, x*y) |
This formula would also result in the number 10.
Using this approach, our initial formula can be rewritten using the LET function:
| =LET ( CubeVal, DBRW(cube,dim1,dim2,dim3), IF(CubeVal=0,”None”, CubeVal) ) |
This ensures that the DBRW pull is only executed once within the cell.
This functionality allows you to optimize your Excel based reports by minimizing the number of calculations that happen within your report.
IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!
Read more Excel Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula
IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function
IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP
