The catch has always been that where values have not been set and are effectively null, you needed to use an IIF() statement to trap for this and resolve by substituting a "0".
The VAL() function seems to be the function we all wanted but did not know about e.g.
Code: Select all
FILTER({TM1SUBSETALL( [Period] )}, VAL(LEFT([Period].CurrentMember.Name,4))=2022)
Code: Select all
FILTER(
{TM1SUBSETALL( [Scenario] )},
VAL([Scenario].CurrentMember.Properties("Flag"))=1)
Remember that numeric attributes are actually treated as strings so my flag with a value of 1 is actually seen as "1.000000". Using VAL() I can simply compare the value in the attribute or cube to a numeric value.
There are some other lesser known functions that may be worth mentioning too:
LEN(<string>) - like Excel, get the length of something
LEFT(<string>,<characters>)
RIGHT(<string>,<characters>)
UCASE(<string>) - Upper case
LCASE(<string>) - Lower case
CASE e.g.
Code: Select all
{STRTOMEMBER("[Period].[" +
CASE LEFT(RIGHT(SetToStr({[Period].[Current Month].Item(0)}),9),7)
WHEN '2021/03' then '2021-Q1'
WHEN '2021/04' then '2021-Q2'
ELSE
SetToStr({[Period].[Current Month].Item(0)})
END
+"]")}