YTD (PQL)

* 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 "year"
• The optional start month integer is used to flag years that don't begin in January. This is only operational with the attribute formulation.
• The optional start day integer is used to flag years that don't begin on the 1st of the month. This is only operational with the attribute formulation.
• For 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 year specified. This can be further modulated using the optional offsets, to change what day the year 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 YEAR 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 year level. If there is no year level, this function will not work.

Different Function types

• This attribute formulation is similar the full year function, but it calculates the range of dates from the start of the year to the specified date.
• This regular hierarchical formulation is similar to the MDX "YTD" function.

Examples

The YTD function using a date of 2016 -06-05 produces a list of dates starting Jan 1 2016 -June 5 2016 when applied to the flat hierarchy called "dateKey".

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

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

Ytd( [data].[dateKey].[1454630400000], 2, 15 )

The YTD function using a member May 2018 produces a list of months starting with the first child of "2018" through to this member, since 2018 is marked as a member in the year level in the regular hierarchy called "Date".

YTD([Date].^[Hierarchy].[2018].[Q2].[May])