Example Substitution Variable Expressions
- 2 Minutes to read
- Print
- DarkLight
- PDF
Example Substitution Variable Expressions
- 2 Minutes to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
Overview
This topic is intended for administrators and designers and provides an example of expressions, syntax, and expression chains.
Consider the following dimension hierarchy for a fiscal year that begins in July each year. Assume that the model contains only these three years.
| Time Dimension | ||
|---|---|---|
FY2017 | FY2018 | FY2019 |
Q1 2017 | Q1 2018 | Q1 2019 |
|
|
|
|
|
|
|
|
|
Q2 2017 | Q2 2018 | Q2 2019 |
|
|
|
|
|
|
|
|
|
Q3 2017 | Q3 2018 | Q3 2019 |
|
|
|
|
|
|
|
|
|
Q4 2017 | Q4 2018 | Q4 2019 |
|
|
|
|
|
|
|
|
|
Expression Syntax
The following expressions are available for use with substitution variables. Assume that @CurYear@ is FY2018 and @CurMth@ is Jul 2017.
Expression | Offset # Required? | Description | Example Syntax | Resulting Value |
FirstChild | No | Returns the first child in the hierarchy. | @CurYear@.FirstChild | Q1 2018. CurYear is FY2018 and its First Child is Q1 2018. |
LastChild | No | Returns the last child in the hierarchy. | @CurYear@.LastChild | Q4 2018. CurYear is FY2018 and its Last Child is Q4 2018. |
FirstSibling | No | Returns the first sibling in the hierarchy, at the same level | @CurYear@.FirstSibling | FY2017. CurYear is FY2018 and its First Sibling is FY2017. |
LastSibling | No | Returns the last sibling in the hierarchy, at the same level | @CurYear@.LastSibling | FY2019. CurYear is FY2018 and its Last Sibling is FY2019. |
Lead | Yes | Returns the next member in the hierarchy, at the same level; skips over parent or child members | @CurYear@.Lead(1) | FY2019. CurYear is FY2018 and the next member at the same level is FY2019. |
|
|
| @CurYear@.Lead(2) | Member Not Found (There is no FY2020 in the model.) CurYear is FY2018 and skipping ahead 2 years would result in the member that follows FY2019 except there is no such year in the model. |
|
|
| @CurMth@.Lead(4)
| Nov 2017. CurMth is Jul 2017 and skipping ahead 4 months is Nov 2017. |
Lag | Yes | Returns the previous member in the hierarchy, at the same level; skips over parent or child members | @CurYear@.Lag(1) | FY2017. CurYear is FY2018 and going back one year is FY2017. |
|
|
| @CurYear@.Lag(2) | Member Not Found (There is no FY2016 in the model.) CurYear is FY2018 and going back two years would result in the member that precedes FY2017 except there is no such year in the model. |
|
|
| @CurMth@.Lag(4)
| Mar 2017. CurMth is Jul 2017 and 4 months before July is Mar 2017. |
Parent | No | Returns the parent member in the hierarchy. | @CurYear@.Parent | Time. CurYear is FY2018 and its Parent is the Time dimension. |
|
|
| @CurMth@.Parent.Parent
| FY2018. CurMth is Jul 2017 and its Parent is Q1 2018, and Q1's Parent is FY2018. |
Expression Chains
You can chain expressions together. Each entry in the expression builds on the results of the previous entry, reading from left to right.
@CurMth@.Parent.FirstChild = Jul 2017. CurMth’s Parent is Q1 2018 and the First Child of Q1 2018 is Jul 2017.
@CurMth@.Lag(3).Parent = Q4 2017. CurMth’s 3-month Lag is Apr 2017 and the Parent of Apr 2017 is Q4 2017
@CurMth@.Parent.Lead(2) = Q3 2018. CurMth’s Parent is Q1 2018 and 2 quarters ahead is Q3 2018.
Note:
We recommend to chain only two expressions together.
When adding functions to a substitution variable, you may find the need to change the order or to remove one of the expressions. Click the menu button next to the expression to make the changes.
If you want to cancel out from adding expressions, click inside the Design Manager or press Esc.
Was this article helpful?