MDX - Using VAL instead of StrToValue
Posted: Wed Apr 27, 2022 5:45 am
For as long as I can remember we have been using StrToValue in MDX when filtering.
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.
What seems to be very useful is you can use VAL() against a numeric attribute e.g.
For C levels and other N levels where flag was never specified and shows a 0, these do not result in an error like StrToValue and the need to use IIF. Changing the above MDX to filter on a value of 0 returns those members either set to 0 or never set.
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.
Hope these extend your MDX knowledge a bit more!
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
+"]")}