When working with Adaptive Planning, especially for the first time as I have recently done, it is a little difficult understanding the time functions and what they return when they are used within time-series sheets (such as the Standard, Cube or Calculated Accounts in Modelled Sheets).
If you’re looking for a brief understanding on what each of these functions return here’s a little sample on what they return. Assumptions about the structure of the data are as follows:
- January 2013 is the beginning of the version .
- January 2014 is the beginning of the plan .
- The fiscal year of the budget is the same as the calendar year – i.e. Jan-Dec.
| Type | Jan-13 (1st month in version) | Feb-13 (2nd month ) | Jan-14 (1st month in budget/plan ) |
|---|---|---|---|
Day(this)
|
15 | 15 | 15 |
DaysInMonth(this)
|
31 | 28 | 31 |
FiscalMonth(this)
|
1 | 2 | 1 |
FiscalQuarter(this)
|
1 | 1 | 1 |
FiscalYear(this)
|
2013 | 2013 | 2014 |
Month(this)
|
1 | 2 | 1 |
Quarter(this)
|
1 | 1 | 1 |
ToDate(Year(this), Month(this))
|
ERR
|
ERR
|
ERR
|
ToDate(Year(this), Month(this), Day(this))
|
ERR
|
ERR
|
ERR
|
VersionMonth(this)
|
0 | 1 | 12 |
VersionYear(this)
|
0 | 0 | 1 |
Year(this)
|
2013 | 2013 | 2014 |
What does
ERR
mean?
-
ERR– The reason why theToDatefunctions return an error is because they return a date value whereas the cell only handles numbers (or percentages). In the example above theToDate( Year( this ), Month( this ), Day( this ) )function would return the following results for each column: - Jan-13 = 2013/01/15
- Feb-13 = 2013/02/15
- Jan-14 = 2014/01/15
However, as the return types can only be of type number or percentage, this return type instead returns
ERR
.
