MENU
    Example Substitution Variable Expressions
    • 2 Minutes to read
    • Dark
    • PDF

    Example Substitution Variable Expressions

    • Dark
    • PDF

    Article summary

    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

    • Jul 2016

    • Jul 2017

    • Jul 2018

    • Aug 2016

    • Aug 2017

    • Aug 2018

    • Sep 2016

    • Sep 2017

    • Sep 2018

    Q2 2017

    Q2 2018

    Q2 2019

    • Oct 2016

    • Oct 2017

    • Oct 2018

    • Nov 2016

    • Nov 2017

    • Nov 2018

    • Dec 2016

    • Dec 2017

    • Dec 2018

    Q3 2017

    Q3 2018

    Q3 2019

    • Jan 2017

    • Jan 2018

    • Jan 2019

    • Feb 2017

    • Feb 2018

    • Feb 2019

    • Mar 2017

    • Mar 2018

    • Mar 2019

    Q4 2017

    Q4 2018

    Q4 2019

    • Apr 2017

    • Apr 2018

    • Apr 2019

    • May 2017

    • May 2018

    • May 2019

    • Jun 2017

    • Jun 2018

    • Jun 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.

    ModelingImagesSubVarsexpress11.png

    If you want to cancel out from adding expressions, click inside the Design Manager or press Esc.


    Was this article helpful?