MDX - Using VAL instead of StrToValue

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:

MDX - Using VAL instead of StrToValue

Post by gtonkin »

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.

Code: Select all

FILTER({TM1SUBSETALL( [Period] )}, VAL(LEFT([Period].CurrentMember.Name,4))=2022)
What seems to be very useful is you can use VAL() against a numeric attribute e.g.

Code: Select all

FILTER(
{TM1SUBSETALL( [Scenario] )},
VAL([Scenario].CurrentMember.Properties("Flag"))=1)
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.

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
+"]")}
Hope these extend your MDX knowledge a bit more!
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: MDX - Using VAL instead of StrToValue

Post by Elessar »

Wow, thanks George!
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: MDX - Using VAL instead of StrToValue

Post by Mark RMBC »

Hi George,

Another wow from me! Just wished you had shared this like 5 years ago!

I knew about all the others except the VAL, though I don't see much need to use UCASE and LCASE since you pointed out the 4th parameter of INSTR could be used to turn off case sensitivity!

regards,

Mark
Post Reply