(2 OM) 14. Formulas Flashcards
Users need to consider the following questions when deciding how to create a specific formula:
- Should the formula change between ___ or be static?
- Should the formula be ___ specific or applied to all levels?
- What formula function do you need to most ___ build the formula?
- Are there ___ ___ that you need to include to target a specific time, level, dimension, or attribute?
- Is there a specific account you need to reference in the formula? Or should you build it using ___ ___ syntax, which does not require an account reference?
- In what ___ should you create the formula, meaning what account type or sheet?
- Versions
- Level
- Efficiently
- Term modifiers
- Dot notion
- Location
The Formula Assistant Tool consists of several parts including…
- Operators
- Functions
- Levels
- Accounts
- Account Term Modifiers
- Formula
- Import Option
- Operators
- Functions
- Accounts
- Account Term Modifiers
- Formula
Which of the below is not considered an operator in the Formula Assistant Tool?
- +
- -
- And
- =
- div ()
- %
- [ ]
- >
- [ ]
The Formula Assistant Tool can be accessed in a variety of locations, including ___ and ___.
Sheets, Accounts.
(Standard, Cube, Modelled sheets.
GL, Custom, Metric,Cube, Modelled Accounts).
Formulas cannot be version specific. True or false?
False (They can be version specific and it depends on the placement of the formula and account).
Which of the below is not an example of formula syntax?
- ACCT.
- ASSUM.
- Div()
- Round()
- [time=this-1]
- +
7.this.year.positionof(this.month)
- +
(1 & 2 are Account syntax, 3 & 4 are Function syntax, 5 is Term Modifier syntax and 7 is Dot Notion syntax).
ROW. syntax appears in ___ sheets and refers to an individual ___ of data, meaning each calculation is independent of each other.
Modelled, row
You would use ROW. syntax when you create calculated modelled accounts that refer to ___ columns, lookup tables and calculated modelled accounts built in that sheet.
Existing
What are the 4 Formula Function types?
- Logical
- Mathematical
- Date
- Boolean
- String
- Logical
- Mathematical
- Date
- String
Use mathematical functions to evaluate basic formulas with ___ outcome.
One
The mathematical function “Div (N, D)” divides the ___ by the ___.
Numerator, denominator
The mathematical function “Divf (N, D)” returns the same result as Div (N, D) but is the faster form in that if the ___ is 0, the numerator is not evaluated.
Denominator
Use logical functions to evaluate based on the outcome of different scenarios. Examples include…
- Or
- If
- Iff
- Isblank
- Error
- Switch
- If
- Iff
- Isblank
- Error
- Switch
The logical function “If (EXPR, T, F)” returns the value of ___ if boolean expression ___ is true, otherwise it returns the value of ___.
T, EXPR, F
The logical function “Iff (EXPR, T, F)” returns the same result as If (EXPR, T, F) but is the faster form in that if the condition is ___, the third argument F is not evaluated. Similarly, if the condition is ___, the second argument T is not evaluated.
True, false
Use Date Functions in flexible ___ models or in combination with ___ sheet Date Element columns.
Time, modelled
Examples of Date Functions include…
- ToDate
- Hour
- Day
- Month
- Version.PositionOf
- TimeFraction
.
- ToDate
- Day
- Month
- Version.PositionOf
- TimeFraction
Use String Functions to evaluate outcomes based on ___-___ data.
Non-numerical
Examples of String Functions include…
- Text
- Concat
- Length
- Search
- Substring
- ToNumber
- Concat
- Length
- Search
- Substring
- ToNumber
Term Modifiers allow a user to further target a formula beyond accounts. The available Term Modifiers are ___, ___, and all existing ___ and ___.
Time, Level, Dimensions, Attributes
Examples of Time Term Modifiers include…
- This+n
- This.year
- This-n
- 2025
- This.Month+Next.Month
- This-12:this-1
- This+n
- This.year
- This-n
- 2025
- This-12:this-1
Level modifiers allow you to create formulas with accounts that reference data from a different ___ than the one the formula is created on.
Level
Does the following Level Term Modifiers follow correct syntax - true or false?
- [Level=<Level-Name>(+)]</Level-Name>
- [Level=<Level-Name>(-)]</Level-Name>
- True (the syntax is including all children from the specified level).
- True (the syntax is excluding all children from the specified level).
For formulas to work across organization levels, the data privacy for an account must be public. True or false?
True
Dimension and attribute modifiers allow you to target specific values entered at a dimension or attribute value intersection. You can then use these values within formulas with accounts to only target specific values, which allows for more ___ planning and reporting.
Granular
You use dot notation syntax in “if” or “iff” functions with an operator <, >, =, <=, >= comparing the statement to a specific value. True or false?
True
Examples of dot notion operands include…
- This.Level
- This.Account.
- This.Dimension_Name
- This.Attribute_Name
- This.Sheet
- This.Version
- This.TimePer
- This.ModelledAccount
- This.Level
- This.Account.
- This.Dimension_Name
- This.Attribute_Name
- This.Version
- This.TimePer
Shared formulas can apply to ___ Levels for a ___ Version (rather than all Versions).
All, specific
Pros of shared formulas are they are ___ and you can ___ and export them.
Cons of shared formulas is they cannot carry ___.
Flexible, import, dimensionality.
Dimensional details can only carry from one account to another by setting the receiving account “Type” to “Link”. True or false?
True
When importing Shared Formulas from an Excel workbook, you can delete shared formulas by leaving the “Formulas” column blank. True or false?
True
Audit trail is inherited when creating a new level or cloning a Level. True or false?
False
You can only clone the lowest child levels. A rollup level is not available for cloning. True or false?
True
Default Formulas are formulas that can be created on any ___ or ___ account.
GL, custom
Default Formulas can be applied to all Versions and Levels using the “Replace” option. Otherwise click “Preserve” which will which will retain all formulas and data in all versions but allow application to the selected version(s). True or false?
True
A pro of default formulas is once applied, they do not have to be maintained as they automatically populate across all ___ and ___. You can also use formula ___ which allows you to adjust the formula as a result of a change in methodology, without impacting all other versions.
Version, Levels, override
Cons of default formulas is that they ___ existing data and ___ account access.
Delete, restricts
In Default Formulas, the override formula setting allows you to ___ a global formula for specific unlocked versions.
Adjust
The override formula setting is available for which of the following types of accounts?…
- GL or Custom accounts that have a default formula applied.
- Metric accounts.
- Calculated modeled accounts.
- Cube calculated accounts or cube metric accounts.
- System Accounts.
- GL or Custom accounts that have a default formula applied.
- Metric accounts.
- Calculated modeled accounts.
- Cube calculated accounts or cube metric accounts.
What is considered best practice for Formula Syntax?
- Use Formula Assistant.
- Consider the order of logic.
- Use indents, lines and comments (#).
- Keep account codes consistent, simple and short.
- Break complex formulas apart.
- Daisychain formulas.
- Use Formula Assistant.
- Consider the order of logic.
- Use indents, lines and comments (#).
- Keep account codes consistent, simple and short.
- Break complex formulas apart.
What is considered best practice for Formula References?
- Use hardcoded values.
- Use time modifiers which are dynamic, rather than dates.
- Divf and Iff evaluate faster than Div and If.
- Use time modifiers which are dynamic, rather than dates.
- Divf and Iff evaluate faster than Div and If.
Once formulas are built, ___ and ___ them and ___ any formula errors.
Validate, test, clear.
The most common formula errors are caused by…
- Not using dot notion syntax.
- Circular references.
- Incorrect syntax
- Data privacy (Account is not set to “Value of account is public at all levels”)
- Circular references.
- Incorrect syntax
- Data privacy (Account is not set to “Value of account is public at all levels”)
Shared formulas ___ be overridden by data imports or data entry.
Can
You can create Default Formulas apply to ___ Levels and Versions. This will set an account to read-only.
You can create Shared Formulas to apply to ___ Levels and ___ Version(s).
All, all, specific.
