Formulas: Do any formulas use the SELECT function, especially with time?
When is this a problem?
Using SELECT is considered hardcoding and not recommended.
It is OK to use SELECT for versions and general time periods.
Why is this a problem?
Using SELECT is hard coding and not recommended. When you use SELECT with a specific list item or time, you limit the ability to update those list items. This is especially an issue with time because a change will be required every year. You’ll need to locate every line item that references the SELECT function and make updates. These changes can only be performed by a Workspace Administrator (WSA), compared to the solution we recommend, which allows an end user to make changes.
How to correct:
Create a module with no dimensions to hold assumptions for Time, and other "SELECT" values or use LOOKUP instead of SELECT. For example, in this module, create a line item called Current Year, format it as a Time Period and select year. You can then use a lookup formula to reference this line item. When the current year changes, you update the value in that line item and it will automatically update all the formulas that reference it.
More information:
Comments
-
Here is an example to demonstrate above mentioned thing.
This is my Source Module where it contains Headcount details by Departments (Pages) and Locations (Rows) and Time (Columns).
Here is my Target module where Headcount should be shown for a particular year. This can be done using SELECT , LOOKUP functions. Using SELECT, you need to hardcode. Formula shall be modified if you wish to see another year data. In order to avoid Hardcoding and to have dynamic update of data, you can use LOOKUP. For that, create an assumptions module with having only line items. Insert "Select Year" line item formatted as Time-Period(Year). Reference this line item in the Headcount formula to lookup data from Source.
2
Model Optimization Checklist
- Step 1 - Lists
- Does the list use Time as part of the key?
- Does the list use a combination of properties as a code?
- Does the list have a code?
- Does the list use Properties?
- Are there subsets on larger lists?
- Does the subset list follow proper naming conventions?
- Does the list include a top level when it is not necessary?
- Step 2 - Modules
- What dimensions are used in the module? Are they all necessary?
- What time dimensions are being used in the module? Can a Time Range be used?
- Are the dimensions in order?
- If there are any saved views, do they use the correct filtering?
- If there are saved views, are all of the line items in the view necessary?
- Is the default view clean?
- Step 3 - Line Items
- Which line items do not need a summary?
- Are text formatted line items overused?
- Are there more than 50 line items in a module?
- Are there excessive subsidiary views?
- Are there line items used as headers where the format is not set to No Data?
- Step 4 - Formulas
- Are there line items that can be placed in a systems module rather than within a calculation module?
- Are there any formulas that that combine text strings?
- Are there any formulas that are daisy chains?
- Are there long formulas, such as complex IF THEN ELSE statements?
- Is there an excessive use of the FINDITEM function?
- Are there SUM and LOOKUP combinations?
- Is the TEXTLIST function used?
- Do any formulas use the SELECT function, especially with time?
- Do any of the line items use conditional formatting with summaries turned on?
- Step 5 - Actions
- Are all the actions necessary?
- Do any of the actions result in errors when they are run?
- Check the data sources. Are some of them lists or modules?
- Check the data sources. Does each data source have an action associated with it?
- Step 6 - Time
- Is the main time scale of the model larger than it needs to be?
- Additional Resources
- Model Optimization Tracker
- Planual