Time Functions
Time Functions and Pov Member References
When using the POV as a point of reference, use the following constants in the Member Scripts. In the constants below, N refers to the number of periods desired.
T#POV (All Dimension Types)
Select Members based on their literal or relative position to the selected main Point of View (POV) or the Cube View POV. The main POV settings are available under the OnePlace module. Call out a Dimension in order to pull its Member value from what is selected in the POV in this way: E#POV, C#POV, T#POV, V#POV, A#POV, ... (all Dimension types).
T#PovYear (Uses Time Member Suffix, e.g., T#PovYearM1)
A specific time period within a current POV year defined by the user’s POV. If POV is equal to 2013M3, then T#POVYear returns 2013. Also, T#POVYearM3 returns the third month of the current POV year.
T#PovPriorYear (Uses Time Member Suffix, e.g., T#PovPrior YearM12)
A specific time period within the prior year defined by the user’s POV e.g., T#POVPriorYearM12
T#PovPriorinYear1 (Uses Integer Suffix, e.g., T#PovPriorInYear1)
This returns the relative period within the same year. If the current period is 2013M3, and T#POVPriorInYear12 is requested, it returns no value.
T#PovPrior1 (Uses Integer Suffix, e.g. T#PovPrior1)
T#POVPrior3 returns the time period three periods prior to the current POV value. If 2013M1 is selected, 2012M10 is returned.
T#PovNextInYear1 (Uses Integer Suffix, e.g., T#PovNextInYear2)
T#POVNextInYear5 returns the month five months in the future from the current POV period selected, but in the same year. If 2013M8 is selected, and T#POVNextInYear9 is requested, it returns no value.
T#PovNext1 (Uses Integer Suffix, e.g., T#PovNext1)
T#POVNext5 returns the month five months in the future from the current POV period selected. If 2013M8 is selected, and T#POVNext9 is requested, 2014M5 is returned.
T#PovFirstInYear
This returns the first period in the year portion of the POV period. It is the same as T#POVM1, T#POVQ1, or, T#POVH1 based on the Input Frequency setting of the given Scenario. T#M1, T#Q1, or, T#H1 work the same way as well.
T#PovLastInYear
This returns the last period in the year portion of the POV period.
T#POVFirstInMonthPrior1
POV Month Example
This refers to the POV month and returns N months prior. If the POV is 2017M10, and T#POVFirstInMonthPrior5 is requested, 2017M5 is returned.
POV Week Example
This refers to the POV week and goes back N weeks from the first week in that month. This varies by calendar (445, 454, 544). In a 445 calendar, if the POV is 2017W38 (M9), and T#POVFirstInMonthPrior5 is requested, the result is driven by the first week in the current month which is 2017W35. This returns 2017W30.
T#PovFirstInQuarterPrior1 (Uses Integer Suffix, e.g., T#PovFirstInQuarterPrior 1)
This refers to the POV sub-period’s quarter and goes back N periods from the first period in that quarter. If the period is 2013M5, and T#POVFirstInQuarterPrior2 is requested, 2013M2 is returned.
T#PovFirstInHalfYearPrior1 (Uses Integer Suffix, e.g., T#Pov FirstInHalfYearPrior2)
This refers to the POV sub-period’s half year and goes back N periods from the first period in that half year. If the period is 2013M5, and T#POVFirstInHalfYearPrior2 is requested, 2012M11 is returned.
T#PovFirstInYearPrior1 (Uses Integer Suffix, e.g., T#Pov FirstInYearPrior3)
This refers to the POV year and goes back N periods from the first period. If the period is 2013M5, and T#POVFirstInYearPrior1 is requested, 2012M12 is returned.
T#PovLastInYearPrior1 (Uses Integer Suffix, e.g., T#PovLastInYearPrior4)
This refers to the POV year and goes back N periods from the last period. If the period is 2013M5, and T#POVLastInYearPrior1 is requested, 2013M11 is returned.
Time Functions and Workflow References
T#WF (e.g., T#WF or S#WF)
This refers to the time period or Scenario currently selected in the Workflow module. Examples are T#WF and S#WF.
T#WFYear (Uses Time Member Suffix, e.g., T#WFYearM1)
A specific time period within the current Workflow year. If the Workflow period is equal to 2013M3, then T#WFYear returns 2013. Also, T#WFYearM3 returns the third month of the current Workflow year.
T#WFPriorInYear1 (Uses Integer Suffix, e.g., T#WFPriorInYear1)
T#WFPriorInYear1, T#WFPriorInYear2…returns the relative period within the same Workflow year. If the current Workflow time period is 2013M3, and T#WFPriorInYear12 is requested, it returns no value.
T#WFPrior1 (Uses Integer Suffix, e.g., T#WFPrior2)
T#WFPrior3 returns the time period three periods prior to the current Workflow period. If 2013M1 is selected, 2012M10 is returned.
T#WFNextInYear1 (Uses Integer Suffix, e.g., T#WFNextInYear1)
T#WFNextInYear5 returns the month five months in the future from the currently selected Workflow period, but in the same year. If 2013M8 is selected, and T#WFNextinYear9 is requested, it returns no value.
T#WFNext1 (Uses Integer Suffix, e.g., T#WFNext 2)
T#WFNext5 returns the month five months in the future from the current Workflow period selected. If 2013M8 is selected, and T#WFNext9 is requested, 2014M6 returns.
T#WFFirstInYear
This returns the first period in the year portion of the Workflow period. This is the same as T#WFM1, T#WFQ1, or T#WFH1 based on the Input Frequency settings of the given Scenario.
T#WFLastInYear
This returns the last period in the year portion of the Workflow period.
Time Functions and Global References
T#Global (e.g., T#Global or S#Global)
The Application has a Global Time and Global Scenario setting found under the Application Tab|Tools|Application Properties. These settings can be used optionally and can be referenced (e.g. T#Global and S#Global). An example of using this as a reference is to build a Cube View and have Global Time and everything before it appear in columns and the Actual Scenario and everything after it appear as the Forecast Scenario.
T#GlobalYear (Uses Time Member Suffix, e.g., T#GlobalYearM1)
A specific time period within the current Global year. If the Global period is equal to 2013M3 then T#GlobalYear returns 2013. Also, T#GlobalYearM3 returns the third month of the current Global year.
T#GlobalPriorInYear1 (Uses Integer Suffix, e.g., T#GlobalPriorInYear1)
T#GlobalPriorInYear1, T#GlobalPriorInYear2…returns the relative period within the same Global year. If the current Global time period is 2013M3, and T#GlobalPriorInYear12 is requested, it returns no value.
T#GlobalPrior1 (Uses Integer Suffix, e.g., T#Global Prior 3)
T#GlobalPrior3 returns the time period three periods prior to the current Global Period. If 2013M1 is selected, 2012M10 is returned.
T#GlobalNextInYear1 (Uses Integer Suffix, e.g., T#GlobalNextInYear5)
T#GlobalNextInYear5 returns the month five months in the future from the current Global period selected, but in the same year. If 2013M8 is selected, and T#GlobalNextInYear9 is requested, it returns no value.
T#GlobalNext1 (Uses Integer Suffix, e.g., T#GlobalNext5)
T#GlobalNext5 returns the month five months in the future from the current Global period selected. If 2013M8 is selected, and T#GlobalNext9 is requested, 2014M6 is returned.
T#GlobalFirstInYear
This returns the first period in the year portion of the Global period. This is the same as T#GlobalM1, T#GlobalQ1, or T#GlobalH1 based on the Input Frequency setting of the given Scenario.
T#GlobalLastInYear
This returns the last period in the year portion of the Global period.
For more Time Function examples, refer to the Samples Tab in the Member Filter Builder.
Time Functions and General References
With these time functions, reference the year separate from the sub-period wherever T# is used. For example, if a user selects a time Member from a Parameter prompt. The user’s options are a long list of time periods (e.g., 2012M6). The Parameter is named MyTimeParam.
The Cube View can use the selection like this:
T#Year(|!MyTimeParam!|)Period(|!MyTimeParam!|)
The Year function returns the specified year and the Period function returns the period without the year. The result is a valid time Member because they are combined. If the user selected 2012M6, it looks like this after the Parameter substitution.
T#Year(2012M6)Period(2012M6)
The result used in the Cube View is:
T#2012M6
This is an example showing last year’s values for the same month:
T#YearPrior1(|!MyTimeParam!|)Period(|!MyTimeParam!|)
which ends up being T#2011M6
This is an example showing last month’s values for the same year:
T#Year(|!MyTimeParam!|)PeriodPrior1(|!MyTimeParam!|)
which ends up being T#2012M5
This is an example showing all base Members for the year:
T#Year(|!MyTimeParam!|).Base
Here are some additional examples:
T#Year(2012M6)Period(2012M6) = 2012M6 T
The 2012M6 could have started as a substitution variable such as |!MyTimeParam!| or |PovTime|
T#Year(2012M6)M2 = 2012M2
T#YearPrior(2012M6)Period(2012M6) = 2011M6
T#YearPrior2(2012M6)Period(2012M6) = 2010M6
T#YearPrior2(2012M6) = 2010
T#Year(2012M6)PeriodPrior3(2012M6) = 2012M3
T#Year(2012M6)PeriodPrior8(2012M6) = 2011M10
The PeriodPrior8 caused it to change the year too.
T#Year(2012M6)PeriodPriorInYear8(2012M6) = 2012M1
The PeriodPriorInYear8 does not change the year.
If the Year section is skipped, the year comes from the Period section.
T#PeriodPrior3(2012M6) = 2012M3
Time functions to use this way:
T#Year(): T#Year(|!TimeParam!|)Period(|!TimeParam!|)
To look at the Time dimension in your workflow and then count back 2 years prior:
T#YearPrior1(): T#YearPrior2(|WFTime|)
WorkFlow Time Points to: 2022M1
Results: 2020 YTD Net Income Value
To look at the Time dimension in your Global POV and count forward 3 years:
T#YearNext1(): T#YearNext3(|GlobalTime|)
Global Time Points to: 2022M3
Results: 2025 YTD Net Income Value
T#Period(): T#Year(|!TimeParam!|)Period(|!TimeParam!|)
T#PeriodPrior1(): T#PeriodPrior1(|WFTime|)
T#PeriodNext1(): T#PeriodNext2(|GlobalTime|)
T#PeriodPriorInYear1(): T#PeriodPriorInYear2(2012M6)
T#PeriodNextInYear1(): T#PeriodNextInYear1(|Global Time|)
T#FirstPeriodInQuarter(): T#FirstPeriodInQuarter(|WFTime|)
T#FirstPeriodInQuarterPrior1(): T#FirstPeriodicInQuarterPrior1(2012M6)
T#FirstPeriodInQuarterNext1(): T#FirstPeriodInQuarterNext2(|POVTime|)
T#FirstPeriodInHalfYear(): T#FirstPeriodInHalfYear(|WFTime|)
T#FirstPeriodInHalfYearPrior1(): T#FirstPeriodInHalfYearPrior1(2012M6)
T#FirstPeriodInHalfYearNext1(): T#FirstPeriodInHalfYearNext2(|WFTime|)
T#FirstPeriodInYear(): T#FirstPeriodInYear(|WFTime|)
T#FirstPeriodInYearPrior1(): T#FirstPeriodInYearPrior1(|!TimeParam!|)
T#FirstPeriodInYearNext1(): T#FirstPeriodInYearNext2(|PovTime|)
T#Quarter(): T#Quarter(|WFTime|)
T#QuarterPrior1(): T#QuarterPrior1(2012M6)
T#QuarterNext1(): T#QuarterNext2(|POVTime|)
Weekly Functions
The following Time Functions apply to weekly applications. The following examples are based on a 52 Week 445 calendar.
T#FirstPeriodInMonth(): T#FirstPeriodInMonth(2017W43)
Returns: 2017W40
T#FirstPeriodInMonthPrior1(): T#FirstPeriodInMonthPrior3(|WFTime|)
Workflow Time: 2017W1
Returns: 2016W50
T#FirstPeriodInMonthNext1(): T#FirstPeriodInMonthNext2(2017W49)
Returns: 2017W50
T#Month(): T#Month(2017W40)
Returns: 2017M10
T#MonthPrior1(): T#MonthPrior3(2017W44)
Returns: 2017M8
T#MonthNext1(): T#MonthNext5(|POVTime|)
POV Time: 2017W9
Returns: 2017M8
Changing the Time Label in Headers When Using These Time Functions
When using this Time function to change a header label in a Cube View, use the :Name(“”) function and substitute what will be seen. For example, to show the value from the last period from the year prior to the Global Time period, use this function:
This example is using 2012M6 as the current Global Time period.
T#YearPrior1(|GlobalTime|)M12
2011M12 will return in the header of the Cube View, such as a column header. In order to return Year End 2011, use the Name function in this way:
T#YearPrior1(|GlobalTime|)M12:Name(“Year End |MFYear|”)
The MF in the substitution variable above stands for Member Formula.
These are examples of what similar substitution variable will return:
T#YearPrior1(|GlobalTime|)M12:Name(“Year End |MFTime|”) returns Year End 2011M12
T#YearPrior1(|GlobalTime|)M12:Name(“Year End |MFTimeDesc|”) returns Year End Dec 2012
T#YearPrior1(|GlobalTime|)M12:Name(“Year End |MFTimeShortDesc|”) returns Year End Dec
T#YearPrior1(|GlobalTime|)M12:Name(“Year End |MFSubPeriod|”) returns Year End M12