Defining Properties of Return Values

The return values of the placeholders for data levels can be specified with additional parameters:

You can use the Period parameters to dynamically assign a heading to the columns of a time series. When the Period parameter is used in the tilde formula for data levels, the return value of the placeholder %short and %long depends on the selected period. 

Structure of the Tilde Formula with Placeholder 'Data level' and Parameter 'Period'

The tilde formula must be structured as follows:

~Data level->"text:%short/ %long”";period

Return Values of the Actual Data Level

Specifying a period for actual data levels gives the following return values:

• If the period lies completely within the actual data level, the return value is Actual.
• If the period lies partially outside the actual data level, the return value is Partly outside of Actual.
• If the period lies outside the actual data level, the return value is Outside of Actual.

Return Values for Planning Data Levels

Specifying a period for plan data levels returns the following values:

• If the period lies completely within the plan data level, the return value is Planning.
• If the period lies before the plan data level and completely within the actual data level, the return value is Actual.
• If the period lies completely within both the actual and plan data levels and intersects both, the return value is Actual/forecast.
• If the period lies partially outside the plan data level, the return value is Partly outside of Planning.
• If the period lies outside the plan data level, the return value is Outside of Planning.

Example: Return Value Depending on the Period 

The Excel worksheet contains three placeholders for data levels that are specified before generation of the Excel report in Lucanet. ~quarter is used as a placeholder for the period:

The return value for periods can be influenced using the help of parameters. The case of the parameter defines whether the first or last month of the time interval is displayed in MS Excel.

Unlike for all other parameters, a distinction is made between upper case and lower case.

Structure of the Tilde Formula for a Period with Optional Parameter

The tilde formula must be structured as follows:

~Period->"Text Parameter"

First Month in the Time Interval

If the period is to refer to the first month of a time interval, the keyword for the parameter is written in lower case. The following keywords are available:

• %d: Returns the first day of the time interval (one-digit or two-digit)
• %dd: Returns the first day of the time interval (two-digit)
• %m:: Returns the first month of the period as one or two digit(s)
• %mm: Returns the first month of the period as two digits
• %mmm: Returns the abbreviation for the first month of the period range
• %mmmm: Returns the name of the first month of the period range
• %yy: Returns the year of the first month of the duration in two digits
• %yyy: Returns the year of the first month of the period range as four digits
• %q: Returns the quarter of the first month of the period range as Arabic numeral
• %qq: Returns the quarter of the first month of the period as a Roman numeral

Last Month in the Time Interval

If the period is to refer to the last month of a time interval, the keyword for the parameter is written in upper case. The following parameters are available:

• %D: Returns the last day of the time interval (one-digit or two-digit)
• %DD: Returns the last day of the time interval (two-digit)
• %M: Returns the last month of the period as one or two digits
• %MM: Returns the last month of the period range as two digits
• %MMM: Returns the abbreviation for the last month of the period range
• %MMMM: Returns the name of the last month of the period range
• %YY: Returns the year of the last month of the duration in two digits
• %YYYY: Returns the year of the last month of the period range as four digits
• %Q: Returns the quarter of the last month of the period as an Arabic numeral
• %QQ: Returns the quarter of the last month of the period range as Roman numeral

Default Display for Periods

If no parameter is specified, then periods are displayed as follows:

• Month: Jan 2017
• Quarter: Q1.2017
• Year: 2017

If the time interval comprises several periods, the time period is displayed as follows:

• Several months: Jan 2017-Feb 2017
• Several quarters: Q1.2017-Q2.2017
• Several years: 2017-2018

Examples

The Excel worksheet contains different tilde formulas for displaying the period:

Tilde formulas with placeholders can be used to display the value of an attribute in MS Excel. The attribute key is used as a parameter for returning the attribute value.

Structure of the Tilde Formula for Returning the Value of an Attribute

The tilde formula must be structured as follows:

~Placeholder->"Text %short/%long";Attribute key

Note: Only attribute values of attributes that can be selected as display language and that are used in the corresponding dimension can be read in to MS Excel.

Example

The ~data level placeholder is used without an attribute and with the attribute keys name_eng and long_name: