MTD

Returns a member list from the start of the month to the specified date member.

  • Returned Output: List
  • Library: PQL \ Semantic \ Date-Time
  • Version: 2020.10.000
  • Compatibility: Pyramid Query Language (PQL) data sources

Syntax

MTD( <Member> , OPTIONAL <Day Integer> )

* Click on the function's arguments above for more details on the input values.

Comments
  • The member needs to be an existing member from either:
    • a date-time flagged attribute in the model.
    • a regular, multilevel hierarchy where there is a level of type "month"
  • The optional start day integer is used to flag months that don't begin on the 1st. This is only operational with the attribute formulation.
  • Details on how to employ and use this function see the semantic calculation overview.
Attribute vs Regular Hierarchy Formulations

When this function is used on a flat attribute / column of type "date-time", the function builds a set of members starting from the start of the month specified. This can be further modulated using the optional day integer offset, to change what day the month is supposed to start from. In effect, the dates in the attribute drive the logic based on their actual date-time values.

When the function is used on a regular hierarchy (with multiple levels), the function returns all the elements in the same level starting with the first one found under the same parent item in the MONTH level all the way up to itself. In effect, the logic ignores the date-time value of the elements and instead resolves the calculation based on the hierarchy and the parent item found specifically in the month level. If there is no month level, this function will not work.

Different Function types
  • This attribute formulation is similar the full month function, but it calculates the range of dates from the start of the month to the specified date.
  • This regular hierarchical formulation is similar to the MDX "MTD" function.

Examples

The MTD function using a date of 2016-02-05 produces a list of dates starting Feb 1 2016 -Feb 5 2016 when applied to the flat hierarchy called "dateKey".

Mtd( [data].[dateKey].[1454630400000] )

The MTD function using a member 2020-05-15 produces a list of dates starting with the first child of "May" through to this member, since May is marked as a member in the month level in the regular hierarchy called "Date".

Mtd( [Date].^[Hierarchy].[2008].[May].[2020-05-15] )