TM1 / Planning Analytics - MDX Reference Guide

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

TM1 / Planning Analytics - MDX Reference Guide

Post by gtonkin »

This post will hopefully get more developers using MDX within their sets and views.
The intention is to expose MDX commands that work in TM1, their syntax or usage and some examples or applications.

This is a guide and not gospel - I am sure there are many ways of doing the same thing as well as better ways.
You will see that some examples switch around certain functions - this may be by design or accidental.

There are also gaps as this project is a work in progress. Similarly, alignment of syntax needs to be reviewed and edited.

Most importantly was getting something out here to begin with. Just like with any TM1 model, something is better than nothing.
Expect errors and omissions but please feedback so that I can update/correct for everyone's benefit.

I am not going to try and replicate any websites or MDX books of which there are many.
My go-to reference is Multidimensional Expressions (MDX) Reference - SQL Server 2012 Books Online, Microsoft


My examples are based on a simple model with an Expense cube built with a Period, Cost Centre and other dimensions.
My Period dimension is deliberately designed with leaf elements as YYYY-MMM e.g. 2021-JAN - these do not sort chronologically and is perfect for some examples.
Also, I have assumed a July start or Period 1.
My Period dimension has multiple rollups grouping Periods into Fiscal Years as well as Periods into Quarters, Halves and Fiscal Years.
These have also been configured in the }HierarchyProperties cube to drive some of the Level queries.
You can create your own Period dimension per below to play with the examples.
Period-Fiscal Years and Halves.png
Period-Fiscal Years and Halves.png (11.14 KiB) Viewed 20813 times
}HierarchyProperties-Default.png
}HierarchyProperties-Default.png (13.39 KiB) Viewed 20813 times
The Cost Centre dimension is simple containing a Total with children. The leaf elements have two attributes that are used in the examples: Primary Cost Centre and Division.
You can configure your own dimension accordingly to test some of the examples.


I also created a basic Client Assumption cube to read the Current Period for the user in some examples.
You could create this per the below cube view.
Client Assumption-Default.png
Client Assumption-Default.png (7.63 KiB) Viewed 20813 times
That should suffice for most of the model needed to test the examples. You can create other objects as required based on the examples.

TM1 / PLANNING ANALYTICS MDX REFERENCE GUIDE


ARITHMETIC OPERATORS
+ Add, Positive
/ Divide
* Multiply
- Subtract, Negative
^ (Power)

BITWISE OPERATORS
ANDPerforms a logical conjunction on two numeric expressions.<Expression 1> AND <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Cost Centre].CurrentMember.Properties("Primary Cost Centre") <> "" AND
[Cost Centre].CurrentMember.Properties("Division") <> ""
) }

Should give you Cost Centres where there is both a value in the Primary Cost Centre and Division attributes.
NOTPerforms a logical negation on a numeric expression.NOT <Expression 1> { FILTER(
{TM1SUBSETALL( [Cost Centre] )},
NOT [Cost Centre].CurrentMember.Properties("Primary Cost Centre") <> "" AND
[Cost Centre].CurrentMember.Properties("Division") <> ""
) }

Should give you Cost Centres where the Primary Cost Centre attribute is blank and Division attributes is not blank.
ORPerforms a logical disjunction on two numeric expressions.<Expression 1> OR <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Cost Centre].CurrentMember.Properties("Primary Cost Centre") = "" OR
[Cost Centre].CurrentMember.Properties("Division") = ""
) }

Should give you Cost Centres where Primary Cost Centre, Division attributes or both are blank.
XORPerforms a logical exclusion on two numeric expressions. Returns True when only one expression evaluates to True. Either Expression 1 or Expression 2 is True, not both.<Expression 1> XOR <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Cost Centre].CurrentMember.Properties("Primary Cost Centre") = "" XOR
[Cost Centre].CurrentMember.Properties("Division") = ""
) }

Should give you Cost Centres where either the Primary Cost Centre or the Division attributes are blank, but not both.
ISPerforms a logical comparison on two object expressions.
Use square brackets if your level contains a space e.g. [Fiscal Year]
Best to fully qualify using dimension and value e.g. [Period].[Quarters] where the name may appear in other dimensions causing ambiguity.
<Expression 1> IS (<Expression 2> | NULL )FILTER({TM1SUBSETALL( [Period] )},
[Period].CurrentMember.Level IS [Period].[Quarters])

Gives you Periods linked to the same level as which Quarters was defined.

COMPARISON OPERATORS
=Is Equal To - Applies to numeric and string comparisons<Expression 1> = <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Cost Centre].CurrentMember.Properties("Primary Cost Centre") = ""
) }

Returns a set of Cost Centres where the Primary Cost Centre attributes is blank.
<>Is Not Equal to - Applies to numeric and string comparisons<Expression 1> <> <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Cost Centre].CurrentMember.Properties("Primary Cost Centre") <> ""
) }

Returns a set of Cost Centres where the Primary Cost Centre attributes is not blank.
>Is Greater Than<Expression 1> > <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Expense].(...,[Amount]) > 50000
) }

Returns a set of Cost Centres where specified value in the Expense cube is greater than 50,000
>=Is Greater Than or Equal To<Expression 1> >= <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Expense].(...,[Amount]) >= 50000
) }

Returns a set of Cost Centres where specified value in the Expense cube is greater than or equal to 50,000
<Is Less Than<Expression 1> < <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Expense].(...,[Amount]) < 0
) }

Returns a set of Cost Centres where specified value in the Expense cube is negative.
<=Is Less Than or Equal To<Expression 1> <= <Expression 2>{ FILTER(
{TM1SUBSETALL( [Cost Centre] )},
[Expense].(...,[Amount]) <= 0
) }

Returns a set of Cost Centres where specified value in the Expense cube is zero or negative.
TRUENot an operator but a literal used in comparisons/filters
FALSENot an operator but a literal used in comparisons/filters

CONCATENATION OPERATORS
+Combine or join multiple strings<String 1> + <String 2>{StrToMember("[Period].[Period].[" +
[Client Assumption].( StrToMember("[}Clients].[" + UserName + "]"),
[Client Assumption Measures].[Current Period]) + "]")}

Concatenates the value from an assumptions cube with the hierarchy to create a valid member.

SET OPERATORS
- (Except)Returns the difference between two sets, removing duplicate members. This operator is functionally equivalent to the Except function.<set expression 1> - <set expression 2> Did not work in a Dimension set but may work with a SELECT statement
* (Crossjoin)Returns the cross product of two sets. This operator is functionally equivalent to the Crossjoin function.
Used in an MDX view where you have dimensions stacked e.g. Product within Customer.
<set expression 1> * <set expression 2> SELECT {TM1SubsetToSet([Expense Measures].[Expense Measures],"Default","public")} ON 0, {TM1SubsetToSet([Cost Centre].[Cost Centre],"Default","public")} * {TM1SubsetToSet([Reporting Currency].[Reporting Currency],"Default","public")} ON 1 FROM [Expense] WHERE ...
: (Range)Returns a naturally ordered set, with the two specified members as endpoints and all members between the two specified members included as members of the set.<Member 1> : <Member 2>[Period].[Period].[2021-JAN] : [Period].[Period].[2021-DEC]

Should return periods from Jan to Dec
+ (Union)Returns a union of two sets, excluding duplicate members.<set expression 1> + <set expression 2> {[Period].[Period].[2020-JAN] : [Period].[Period].[2020-DEC]} +
{[Period].[Period].[2022-JAN] : [Period].[Period].[2022-DEC]}

Will return a set with the range of members from the first expression and second expression. Any overlaps do not result in duplicates.

STRING FUNCTIONS
NameReturns a string containing the member name<Member expression>.NameFILTER({TM1SUBSETALL( [Period] )},
RIGHT([Period].CurrentMember.Name,1)="V")

Returns elements where the Name of the member ends in a V e.g. 2021-NOV
UniquenameReturns a string containing the member unique name.
Values are enclosed in square brackets.
<Member expression>.UniqueNameFILTER({TM1SUBSETALL( [Period] )},
INSTR(1, [Period].CurrentMember.UniqueName, "^", 0) = 0)

Returns members that are unique in the hierarchy i.e. do not form part of any other rollups and thus are not further qualified with a caret (^)
PropertiesReturns a string, or a strongly-typed value, that contains a member property value i.e. Attribute value.Member_Expression.Properties(Property_Name [, TYPED]){ORDER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Period] )}, 0)},
[Period].CurrentMember.Properties("Month"),
BASC)}

Returns a set of leaf level months orders by Month which is numeric.
2021-JAN, 2021-FEB etc. do not sort chronologically naturally.
GenerateExecutes a string function on each member of the set, almost like a For Each Member statement.Generate(<set expression 1>, <set expression 2> [, ALL]){GENERATE(
{[Period].[2023-MAR]},
{[Period].CurrentMember,
StrToMember("[Period].[" + [Period].CurrentMember.Name + " YTD]"),
[Period].CurrentMember.Parent,
[Period].CurrentMember.LAG(12)}
)}

Should return 2023-MAR itself, being the CurrentMember then 2023.09 YTD, 2023.FY and 2022-APR
UserNameReturns the domain name and user name of the current connection.UserName{StrToMember("[Period].[Period].[" +
[Client Assumption].( StrToMember("[}Clients].[" + UserName + "]"),
[Client Assumption Measures].[Current Period]) + "]")}

UserName is the string value the active client and used to lookup the current period in the assumption cube. In this case, my value in the cube is 2021-JAN and this is returned as a member.
InstrReturns the position of the first occurrence of one string within another.
NB: 4th parameter, compare in TM1 is used as 0 for case-sensitive, 1 for ignore case.
InStr([start, ]searched_string, search_string[, compare]){FILTER(
{[Period].[Period].Members},
InStr(1, [Period].[Period].CurrentMember.Name, "ju",1)>0
)}

Returns periods containing JU, Ju, jU or ju - remember parameter 4 to manage case.
UCaseConvert a string to upper caseUCase(<string>){FILTER(
{[Period].[Period].Members},
InStr(1, UCase([Period].[Period].CurrentMember.Name), "JU",0)>0
)}

Returns periods containing JU e.g. 2021-JUL
LCaseConvert a string to lower caseLCase(<string>){FILTER(
{[Period].[Period].Members},
InStr(1, LCase([Period].[Period].CurrentMember.Name), "ju",0)>0
)}

Returns periods containing JU e.g. 2021-JUL
LeftReturn n number of characters from the start of a stringLeft(<string>,<count>){FILTER(
{[Period].[Period].Members},
LEFT([Period].[Period].CurrentMember.Name, 4)="2022"
)}

Returns periods where the first 4 characters are 2022 - could be N or C or S elements.
RightReturn n number of characters from the end of a stringRight(<string>,<count>){FILTER(
{[Period].[Period].Members},
RIGHT([Period].[Period].CurrentMember.Name, 3)="JUL"
)}

Returns periods where the last 3 characters are JUL - could be N or C or S elements. Does not appear case sensitive as it would include as a match 2021-Jul, 2021-jul too.

CASTING FUNCTIONS
MemberToStrReturn a string containing the uniquename of the memberMemberToStr(<Member>){FILTER(
{[Period].[Period].Members},
MemberToStr([Period].[Period].CurrentMember)="[Period].[2022.FY^2022-JAN]"
)}
StrToMemberReturns the member as specified in the string expressionStrToMember(<string expression> [, CONSTRAINED])STRTOMEMBER("[Period].[Period].[" +
SETTOSTR(
TM1SubsetToSet([Period].[Period] , "_S-Current Year" , "public")) + "]"
)
StrToValueReturn the value of a specified string. NB: Will fail on Nulls and my require the use of IIF() to trapStrToValue(<string expression>){FILTER(
{[Period].[Period].Members},
StrToValue([Period].[Period].CurrentMember.Properties("LEVEL_NUMBER"))=0
)}
SetToStrReturns a string based on the set expression. Will bring back values with braces and a comma e.g. {2022-JAN}, you may need to trim these.SetToStr(<set expression> )STRTOMEMBER("[Period].[Period].[" +
SETTOSTR(
TM1SubsetToSet([Period].[Period] , "_S-Current Fiscal Year" , "public")) + "]"
).NEXTMEMBER
StrToSetReturn a set as specified by the string in the set specification. MS MDX has CONSTRAINED as an optional 2nd parameter which is not supported in TM1StrToSet(<set specification> [, CONSTRAINED])StrToSet( "[Period].[Period].Members" )
StrToTupleReturns a tuple as specified in the string expressionStrToTuple(<string expression> [, CONSTRAINED])Need an example using SELECT
TupleToStrReturns a string containing tuples. Could be used to parse in other functionsStrToTuple(<tuple expression>)Need an example using SELECT
NameToSetReturns a set based on a member nameNameToSet(<member name>){NameToSet( "[Period].[Period].[2022.FY^2022-JAN]")}

LOGICAL FUNCTIONS
ISPerforms a logical comparison on two object expressions to see if they are exactly the same.<Expression1> IS <Expression2>No useful example of how to use:
FILTER(
{TM1SUBSETALL( [Period] )},
[Period].CurrentMember IS [Period].[2020-JUL]))
IIFReturns one of two values determined by a logical test.IIf(Logical_Expression, Expression1, Expression2)FILTER(
{TM1SUBSETALL( [Period] )},
StrToValue(
IIF([Period].CurrentMember.Properties("Period Index")="",
"0",
[Period].CurrentMember.Properties("Period Index")))>0)
Case, When, Else, EndLets you conditionally return specific values from multiple comparisons.CASE <expression>
WHEN <case 1> then <expression 1>
WHEN..
ELSE
<expression>
END
{ STRTOMEMBER("[Period].[" +
CASE LEFT( RIGHT( SetToStr( {[Period].[Period].[_S-Current Period].Item(0)} ), 10), 8)
WHEN "2022-JAN" then "2021.Q1"
WHEN "2021-APR" then "2021.Q2"
ELSE
SetToStr( {[Period].[Period].[_S-Current Period].Item(0)} )
END
+"]") }
IsLeafTest is a member is a Leaf elementIsLeaf(<Member>)FILTER(
{TM1SUBSETALL( [Period] )},
IsLeaf([Period].CurrentMember))
IsAncestorTest if a specified member is an Ancestor of another specified member.IsAncestor(<Member1>,<Member2>)FILTER(
{TM1SUBSETALL( [Period] )},
IsAncestor([Period].CurrentMember,[Period].[2022.Q3]))
Returns all Ancestors of 2022.Q3
IsEmptyTest if a cell, property or value is emptyIsEmpty(<Expression>)FILTER(
{TM1SUBSETALL( [Period] )},
IsEmpty([Sales].([...]))
IsSiblingTest if a member is a sibling of another specified member.IsSibling(<Member>,<Sibling Member>)FILTER(
{TM1SUBSETALL( [Period] )},
IsSibling([Period].CurrentMember,[Period].[2022.Q3]))
IsGenerationTest if the specified Member is in the specified Generation (Level)IsGeneration(<Member>,<Generation>)FILTER(
{TM1SUBSETALL( [Period] )},
IsGeneration([Period].CurrentMember,2))
IsNullNot supported yetISNULL(MDX_expression,value_if_null)Would be useful to set blanks to "0" etc. avoids IIF()

MEMBER FUNCTIONS AND EXPRESSIONS
AllMembersReturns a set that contains all members of the specified hierarchy or level.
Will include all calculated members in that hierarchy or level.
<hierarchy expression>.AllMembers
<level expression>.AllMembers
[Period].AllMembers or
[Period].[level000].AllMembers or
[Period].[Halves].AllMembers
MembersReturns the set of members in a dimension, level, or hierarchy. Returns Members, not Elements i.e. will repeat where element is in multiple rollups<dimension expression>.Members
<hierarchy expression>.Members
<level expression>.Members
[Period].Members
DefaultMemberReturn the default member for the dimension/hierarchy as specified in the }HierarchyProperties cube.<dimension expression>.DefaultMember{[Period].DefaultMember}
CurrentReturns the current tuple from a set during iteration.<set expression>.CurrentNeed a useful example
CurrentMemberReturns the current member along a specified hierarchy during iteration.Hierarchy_Expression.CurrentMemberFILTER(
{TM1SUBSETALL( [Period] )},
IsLeaf([Period].CurrentMember))
NextMemberReturns the next member in the level that contains a specified member.Member_Expression.NextMember{[Period].[2020-JUN].NextMember}
gives you 2020-JUL
PrevMemberReturns the previous member in the level that contains a specified member.Member_Expression.PrevMember{[Period].[2020-JUN].PrevMember}
gives you 2020-MAY
ParentReturns the parent of a member.Member_Expression.ParentReturns the first parent of the member
{TM1Member([Period].[Current Month].Item(0),0).Parent}
Root
LevelReturns the Level of a memberMember_Expression.Level[Period].[2021-Q3].Level
LevelsReturns members from a specified Level using the Level Name or numberHierarchy_Expression.Levels( Level_Number )
or Hierarchy_Expression.Levels( Level_Name )
[Period].[Period].LEVELS("Quarters").MEMBERS
or [Period].[Period].LEVELS(1).MEMBERS
This
AncestorA function that returns the ancestor of a specified member at a specified level or at a specified distance from the member.Ancestor(Member_Expression, Level_Expression)
or Ancestor(Member_Expression, Distance)
Returns an Ancestor from n levels higher. This returns Total Years which is the parent of 2021
{Ancestor(TM1Member([Period].[Current Month].Item(0),0),2)} or
Returns the Half that the period is in:
{Ancestor([Period].[2021-Q3^2021/07],[Period].[Halves])}
AncestorsReturns a set of ALL ancestorsAncestors(Member_Expression, Level_Expression)
Ancestors(Member_Expression, Distance)
Returns Ascendants without the current member
{TM1Member([Period].[Current Month].Item(0),0).Ancestors}
AscendantsReturns all of the ancestors of a member from the member itself up to the top of the member’s hierarchyAscendants(Member_Expression)Returns the Current Month and parent then its parent
{Ascendants(TM1Member([Period].[Current Month].Item(0),0))}
FirstSiblingReturns the first child of the parent of a member.Member_Expression.FirstSiblingReturns the first child in the rollup that the member is in i.e. 2021/01 in this case
{TM1Member([Period].[Current Month].Item(0),0).FirstSibling}
LastSiblingReturns the last child of the parent of a specified member.Member_Expression.LastSiblingReturns the first child in the rollup that the member is in i.e. 2021/12 in this case
{TM1Member([Period].[Current Month].Item(0),0).LastSibling}
FirstChildReturns the first child of a specified member.Member_Expression.FirstChildReturns 2021/01 given 2021
{[Period].[2021].FirstChild}
LastChildReturns the last child of a specified member.Member_Expression.LastChildReturns 2021/12 given 2021
{[Period].[2021].FirstChild}
CousinReturns the child member with the same relative position under a parent member as the specified child member.Cousin( Member_Expression , Ancestor_Member_Expression )Returns the same month in the specified year i.e. 2023/06:
{Cousin([Period].[Period].[2021/06], [Period].[Period].[2023])}
OpeningPeriodReturns the first sibling among the descendants of a specified level, optionally at a specified member.OpeningPeriod( [ Level_Expression [ , Member_Expression ] ] ){OpeningPeriod([Period].[Period].[Quarters],[Period].[Period].[2022-H2])}
Returns 2022-Q3 as this is the first Quarter in 2022-H2
ClosingPeriodReturns the member that is the last sibling among the descendants of a specified member at a specified level.ClosingPeriod( [ Level_Expression [ ,Member_Expression ] ] ){ClosingPeriod([Period].[Period].[Quarters],[Period].[Period].[2022-H2])}
Returns 2022-Q4 being the last Quarter in 2022-H2.
ParallelPeriodReturns a member from a prior period in the same relative position as a specified member.ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] ){ParallelPeriod([Period].[Period].[Quarters],4,[Period].[Period].[2022-Q4])}
Returns 2021-Q4 which is 4 quarters prior
HeadReturns the first specified number of elements in a set, while retaining duplicates.Head(Set_Expression [ ,Count ] ){HEAD([Period].[2020.FY].Children,6)}
TailReturns a subset from the end of a set.Tail(Set_Expression [ ,Count ] ){TAIL([Period].[2020.FY].Children,6)}
LeadReturns the member that is a specified number of positions following a specified member along the member's level.Member_Expression.Lead( Index ){[Period].[2020-JUL].LEAD(6)}
Gives you 2021-JAN
LagReturns the member that is a specified number of positions before a specified member at the member's level.Member_Expression.Lag(Index){[Period].[2020-JUL].LAG(6)}
Gives you 2020-JAN
ValueDefault property of a member

TUPLE FUNCTIONS AND EXPRESSIONS
CurrentReturns the current tuple from a set during iteration.
ItemThe Item function returns a member from the specified tuple.TM1Member([Period].[Current Year].ITEM(0),0 )

SET FUNCTIONS AND EXPRESSIONS
AddCalculatedMembers
ChildrenReturns the set of children of a specified member.Member_Expression.ChildrenReturns the months in the quarter in this case:
[Period].[2021-Q3].Children
SiblingsReturns the siblings of a specified member, including the member itself.Member_Expression.SiblingsReturn all periods in the year based on current month.
{TM1Member([Period].[Current Month].Item(0),0).Siblings}
DescendantsReview parameters e.g. SELF, ABOVE, LEAVES etc.Descendants(Member_Expression [ , Level_Expression [ ,Desc_Flag ] ] )Descendants(TM1SubsetToSet([Period].[Period], "FiscalPeriods" , "public" ),4,Leaves)
MembersRetrieves a set containing all of the members from a dimension, hierarchy, or level.Hierarchy_Expression.Members[Period].[Period].Members
DistinctEvaluates a specified set, removes duplicate tuples from the set, and returns the resulting set.Distinct(Set_Expression)
HierarchizeOrganize members into the Hierarchical order.Hierarchize(Set_Expression [ , POST ] ){Hierarchize([Period].[Halves].Members + [Period].[Fiscal Years].Members,POST)}
NonEmptyReturns the set of tuples that are not empty from a specified set, based on the cross product of the specified set with a second set.NONEMPTY(set_expression1 [,set_expression2])
CrossJoinIt returns a set of tuples representing the cartesian product of the sets passed into it as parameters.
Enables you to create 'nested' or 'crosstabbed' axes in queries, same as using *
Crossjoin(Set_Expression1 ,Set_Expression2 [,...n] )
UnionReturns a set that is generated by the union of two sets, optionally retaining duplicate members.
IntersectReturns members appearing in both sets. Duplicates can optionally be retained using the ALL parameterIntersect(Set_Expression1 , Set_Expression2 [ , ALL ] )INTERSECT(
TM1SUBSETALL( [Period] ),
TM1SubsetToSet( [Period], "_S-Quarter", "Public" ),
ALL
)
ExceptEvaluates two sets and removes those tuples in the first set that also exist in the second set, optionally retaining duplicates.
ExtractReturns a set of tuples from extracted hierarchy elements.Extract(Set_Expression, Hierarchy_Expression1 [,Hierarchy_Expression2, ...n] )
FilterExclude members based on criteria
OrderOrder contents of a set ASC, DESC, BASC, BDESC
SubsetSkip N number of members and returns the remainder like the MID function for characters. Position is zero-based.Subset(Set_Expression, Start [ ,Count ] )
LastPeriodsReturns a set of members up to and including a specified member.LastPeriods(Index [ ,Member_Expression ] )LastPeriods(4,[Period].[Period].[2022/08])
Returns 2022/08 and the prior 3 periods - 4 members in all
PeriodsToDateReturns a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level in the Time dimension.PeriodsToDate( [ Level_Expression [ ,Member_Expression ] ] )PeriodsToDate([Period].[Period].[Years],[Period].[Period].[2022-Q3^2022/08])
Returns 2022/01 and all periods to 2022/08
BottomCountSorts a set in ascending order, and returns the specified number of tuples in the specified set with the lowest values.BottomCount(Set_Expression, Count [,Numeric_Expression])BOTTOMCOUNT(
TM1SubsetToSet([model].[model] , "_S-All N" , "public") ,
10.0 ,
[SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter]))
Sorts and returns bottom 10 models based on units sold - lowest sales (SData)
BottomPercentSorts a set in ascending order, and returns a set of tuples with the lowest values whose cumulative total is equal to or greater than a specified percentage.BottomPercent(Set_Expression, Percentage, Numeric_Expression)BOTTOMPERCENT(
TM1SubsetToSet([model].[model] , "_S-All N" , "public") ,
10 ,
[SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter]))
Returns models where the aggregate value is within 10% of the total for all models
BottomSumSorts a set and returns the bottom most elements whose cumulative total is at most a specified value.BottomSum(<Set>, Value, Numeric_Expression)BOTTOMSUM(
TM1SubsetToSet([model].[model] , "_S-All N" , "public") ,
1000 ,
[SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter]))
Returns all members where the sum of units is 1000 or less
TopCountSorts a set in descending order and returns the specified number of elements with the highest values.TopCount(Set_Expression,Count [ ,Numeric_Expression ] )TOPCOUNT(
TM1SubsetToSet([model].[model] , "_S-All N" , "public") ,
10.0 ,
[SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter]))
Sorts and returns top 10 models based on units sold (SData)
TopPercentSorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage.TopPercent(Set_Expression, Percentage, Numeric_Expression)TOPPERCENT(
TM1SubsetToSet([model].[model] , "_S-All N" , "public") ,
10 ,
[SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter]))
Returns models where the aggregate value contributes 10% of the total for all models
TopSumSorts a set and returns the topmost elements whose cumulative total is at least a specified value.TopSum(Set_Expression, Value, Numeric_Expression)TOPSUM(
TM1SubsetToSet([model].[model] , "_S-All N" , "public") ,
40000000,
[SalesCube].([actvsbud].[Actual], [region].[Argentina], [account1].[Units], [Month].[1 Quarter]))
Returns members where the sum is at least 40,000,000 - 3 members returned as the first 2 members was below 40 mil and needed a 3rd member to take it over the threshold.
DrilldownLevelDrills down the members of a set to one level below the lowest level represented in the set, or to one level below an optionally specified level of a member represented in the set.DrilldownLevel(Set_Expression [ , Level_Expression ] )
DrilldownLevelTopDrills down the topmost members of a set, at a specified level, to one level below.DrilldownLevelTop(<set_expression>, <count> [,[<level_expression>] [,[<numeric_expression>][,INCLUDE_CALC_MEMBERS]]])
DrilldownLevelBottomDrills down the bottommost members of a set, at a specified level, to one level below.DrilldownLevelBottom(Set_Expression, Count [ , Level_Expression [ ,Numeric_Expression ] ] )
DrilldownMemberDrills down the members in a specified set that are present in a second specified set.DrillDownMember(<set_expression1>, <set_expression2> [,[<target_hierarchy>]] [,[RECURSIVE][,INCLUDE_CALC_MEMBERS]])
DrilldownMemberTop
DrillupLevel
DrillupMember
DrilldownMemberBottom
ToggleDrillState

DIMENSION, HIERARCHY and LEVEL FUNCTIONS
Dimension.NameIntrinsic DIMENSION_NAME
Hierarchy.NameIntrinsic HIERARCHY_NAME
Level.NameIntrinsic LEVEL_NAME
* Used in conjunction with other functions to obtain information about the members of a dimension, hierarchy, or level.

COMMENTING
//Inline comments or own line
--Inline comments or own line
/*…*/Blocked comments

NUMERIC FUNCTIONS
Aggregate
Avg
CoalesceEmptyConverts an Empty but not Null value to a specified valueCoalesceEmpty( <Numeric_Expression> [ ,<Replacement_Numeric_Expression>,...n] )
Count
Correlation
Covariance
CovarianceN
DistinctCount
LinRegIntercept
LinRegPoint
LinRegR2
LinRegSlope
LinRegVariance
LookupCube
Max
Min
Median
Rank
StdDevSame as StDev - Alias
StDev
StdDevPSame as StDevP - Alias
StDevP
Sum
Var
VarianceSame as Var - Alias
VarP
VariancePSame as VarP - Alias
VALReturns the value of a string (Nulls evaluate to 0)FILTER(
{TM1SUBSETALL( [Scenario] )},
VAL([Scenario].CurrentMember.Properties("Flag"))=1)
LENReturns the length of a stringFILTER({TM1SUBSETALL( [Period] )}, LEN([Period].CurrentMember.Name)=4)

TM1 SPECIFIC FUNCTIONS
IBM Documentation
TM1MemberReturns a member from a specified tuple/subset. Avoid using as PAW translates any subset with TM1Member to underlying code.TM1MemberTM1Member([Period].[_S-Current Recharge Quarter].ITEM(1),0 ),
TM1FilterbyPatternReturns members matching a specified pattern.TM1FILTERBYPATTERN( <set>, <pattern_str> )
Undocumented is the 3rd parameter for an attribute:
TM1FILTERBYPATTERN( <set>, <pattern_str>[, <attribute>] )
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, "*JUN*")}
Returns all members containing JUN e.g. 2020-JUN, 2021-JUN

TM1FILTERBYPATTERN(
{TM1SUBSETALL( [Expense Account] )},"*2865*","Group Account Number")
Returns all Expense Accounts where the Group Account Number attribute contains 2865
TM1FilterByLevelReturns members matching a level number (0=Leaf)TM1FILTERBYLEVEL( <set>, <level_number>)
TM1DrillDownMemberDrills down a member like the expand button in TM1TM1DRILLDOWNMEMBER( <set1>, <set2>|ALL [,RECURSIVE] )
TM1SortSorts a set alphabeticallyTM1SORT( <set>, ASC|DESC )
TM1SortByIndexSorts a set by index/ordinalTM1SORTBYINDEX( <set>, ASC|DESC )
TM1SubsetAllReturns all Elements in a dimension, distinct, not all members.TM1SUBSETALL([<dimname>])
TM1SubsetToSetInserts the elements of a set into the current setTM1SubsetToSet(<Dimension>,<Hierarchy>,<SubsetName>[,<Public|Private])
TM1TupleSizeA count of members in the set/tupleTM1TupleSize
TM1SubsetBasis
TM1RollUp
TM1Format_String
TM1Ignore_BadTuples
TM1RuleDerived
TM1Subset_Ident
TM1ToggleDrillState
TM1ToggleExpandMode
TM1Update
TM1Updateable
TM1BangPrefix
Still to come once the above is more complete will be the MDX View related functions and related keywords.
As mentioned at the outset, please feedback to improve this post for everyone.

Thanks to Wim, Adam, Declan, Ryan and others who have added input along the way.
Last edited by gtonkin on Fri Jun 24, 2022 5:06 am, edited 5 times in total.
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by Wim Gielis »

Fantastic ! I hope it can Generate a lot of feedback.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Keith Would
Posts: 13
Joined: Tue Dec 06, 2016 11:24 am
OLAP Product: TM1
Version: 10.3.0
Excel Version: 2010

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by Keith Would »

Thanks folks, this will be very useful.

I find that the set editor in PAW is a useful resource as a starting point for seeing what the MDX should be, so between that and this guide, hopefully that should cover most things that I may want to do.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by jim wood »

This is awesome thank you.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by Alan Kirk »

Great work, George; thank you for doing this.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by gtonkin »

It is a start - there is a lot more to add before it nears completion.
Appreciate any feedback or assistance with more practical examples.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by MarenC »

Hi,

Great work and very useful for us.

Just one thing, can't you use mathematical operators: + - /

to add, subtract and divide by in MDX?

Didn't spot it in your reference guide.

Maren
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by gtonkin »

MarenC wrote: Fri May 27, 2022 8:03 am Hi,

Great work and very useful for us.

Just one thing, can't you use mathematical operators: + - /

to add, subtract and divide by in MDX?

Didn't spot it in your reference guide.

Maren
I had not included the arithmetic operators but added them - if you have some examples, I will add those too.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by MarenC »

Hi,
If you have some examples, I will add those too.
The examples are quite long and on specific models, so if I get the time I will try to recreate on a scaled down test model.

But as an example use case, we have 2 cubes, one for loaded actual data and then a cube for forecasted/Budget data.

In our reconciliation reports, we use mdx to return rows where the value in the actual cube - value in the forecast cube for a given period (plus lots of other dimension references) is greater than a specified value, or could be zero.

Maren
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by PavoGa »

Good work, George!
Ty
Cleveland, TN
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by gtonkin »

I have update the reference above with Wim's latest find:

TM1FilterByPattern has an undocumented 3rd parameter to allow you to match on an attribute, including aliases:
TM1FILTERBYPATTERN( <set>, <pattern_str>[, <attribute>] )

TM1FILTERBYPATTERN(
{TM1SUBSETALL( [Expense Account] )},"*2865*","Group Account Number")

Will return all expense accounts where the Group Account Number contains 2865.

Thanks Wim!
Niko
Posts: 50
Joined: Tue Feb 15, 2022 6:43 am
OLAP Product: IBM Cognos TM1
Version: PA 2.09 / TM1 11.7.00000.42
Excel Version: EXCEL 2019
Location: Asia

Re: TM1 / Planning Analytics - MDX Reference Guide

Post by Niko »

after my test, TM1FILTERBYPATTERN the third parameter(attribute) can not support Chinese
Post Reply