MDX to Filter on Numeric Attribute

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 to Filter on Numeric Attribute

Post by gtonkin »

I am trying to find a better way to filter on a Numeric Attribute (contains 0/1) where the name of the Attribute also happens to be the name of one of the Members.
Using the standard syntax to filter on an attribute returns the context error as the MDX is ambiguous.
Using the following code does not work as the value being evaluated seems to be something other than 1:

Code: Select all

{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)},
[<dim>].CurrentMember.Properties("<attr>")="1")}
Note: Converting the result using StrToValue yields an error confirming that both numeric and string attributes are treated as strings in MDX

Getting a bit more creative, the following does seem to work:

Code: Select all

{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)},
INSTR(1,[<dim>].CurrentMember.Properties("<attr>"),"<value>",0)>0
)}
So,
-Any better ideas to get the MDX more simple for future reviewers/maintainers?
-Anyone know what is returned by [<dim>].CurrentMember.Properties("<attr>") other than a plain old 1?
-Any way to TRIM the result - tried trim, rtrim, ltrim?
-Is there a way to fully qualify the attribute within the MDX, similar to how you would need to address ambiguous elements in Rules?

And lastly, yes, I could have changed the name of the attribute as an easy way out but where's the challenge then?
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: MDX to Filter on Numeric Attribute

Post by tm123 »

You can use the }ElementATtributes_dimName cube in your filter. Can you check if MDX below will give you want you want?

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [<dim>] )}, 0)}, [}ElementAttributes_<dim>] .([}ElementAttributes_<dim>].[<NumericAttribName>])=1)}
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: MDX to Filter on Numeric Attribute

Post by gtonkin »

Hi TM123, it does indeed give me the results-too long looking at a problem from one angle. Thanks for the assistance!
FYI - my initial MDX seems to work fine on PAX 2.0.1 - something must have been fixed.

Still curious though about the answers to some of the other questions posed in my initial post.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: MDX to Filter on Numeric Attribute

Post by lotsaram »

Hey George this looks a lot like a PMR which I had open. 89862,112,848 "Inconsistent behavior in TM1 MDX evaluation"
PMR wrote:There are 3 ways which TM1 allows attributes to be queried via MDX which deliver inconsistent results. TM1 allows both string and numeric attributes

1/ TM1 "shorthand" MDX notation e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)}, [Date].[Serial TM1] > 20000 )}

The issue with TM1 shorthand notation (which is what is traditionally recorded in the architect/perspectives subset editor) is that it is ambiguous whether a member, a subset or an attribute is being referenced which can lead to inconsistency in the case of name conflicts.

2/ TM1 "industry standard MDX" using member properties. e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)} , [Date].CurrentMember.Properties("Serial TM1") > 20000 )}

This syntax is generally preferred as it conforms to Microsoft MDX norms and is non-ambiguous. However in TM1 this notation forces all member properties to type STRING so the above member set query would fail and return an empty set.

3/ "background attribute cube query" e.g.
{ FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Date] )}, 0)}, [}ElementAttributes_Date].( [}ElementAttributes_Date].[Serial TM1] ) > 20000 )}

This member set query will work for a numeric attribute.

All 3 of the above queries work and return consistent results for String type attributes but the standard member properties type notation does not work for numeric attributes and the type is coerced to string. Behavior should be consistent for all 3 expression types.

Currently developers are forced to code a workaround by using the 3rd expression type for numeric attributes.
Support did open a collab with development but they came back with "this is expected behavior." Essentially from the point of view of MDX all member properties are expected as strings and you can't retype them. So the only work around is to query the attribute cube.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
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: MDX to Filter on Numeric Attribute

Post by gtonkin »

Thanks for the response and info Lotsa - will be using option 3 for the foreseeable future as many clients are still on 10.2.2 FP4.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: MDX to Filter on Numeric Attribute

Post by Steve Rowe »

Sort of off topic but...
Isn't this a symptom of the way numeric attribute are implemented in the first place? Numeric attributes aren't really numeric cells in the same way that data is and IMO are just a "mask" on a string attribute. Personally I rarely use them since they behave like strings and I can't write rules versus them.

If I want a numeric property of an element I'll build a cube for this purpose, though lotsarams use case of time is a good example where I wouldn't....
Technical Director
www.infocat.co.uk
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: MDX to Filter on Numeric Attribute

Post by gtonkin »

Steve Rowe wrote: Wed Dec 06, 2017 10:24 am ...Personally I rarely use them since they behave like strings and I can't write rules versus them...
Thanks Steve, agree on the strings and the usage of another cube as you can secure better as well as use picklists to populate the text "attribute".
markhackett
Posts: 3
Joined: Mon Aug 12, 2013 2:00 pm
OLAP Product: IBM Planning Analytics Express
Version: PA 2.0.9.19 and PAW 2.0.91
Excel Version: 2010

Re: MDX to Filter on Numeric Attribute

Post by markhackett »

I realise this is an old post, but I was looking for a way to convert a string attribute to numeric value for checking in an MDX expression. It looks like the StrToValue function allows you to convert the string to a numeric value, so you can use this in your MDX expression, e.g.

{
FILTER(
{TM1FILTERBYLEVEL({TM1SUBSETALL([Dates])},0)},

(StrToValue([Dates].[LeafIndex])>100)
)
}

In the above example, LeafIndex is a text attribute (probably shouldn't be text, but it is). Just wanted to update this post in case anyone else was looking for similar.

Mark
Wim Gielis
MVP
Posts: 3105
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: MDX to Filter on Numeric Attribute

Post by Wim Gielis »

There are a couple more examples with StrToValue on my page (just use Ctrl-F on the page):
https://www.wimgielis.com/tm1_mdxstatements_EN.htm
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
Post Reply