Page 1 of 1
Filter by Attribute
Posted: Tue Dec 01, 2015 10:54 am
by Chuks
Hi All,
We have a dimension with some elements and an attribute associated with it,as below
Dim1
A
B
C
Attributes ( in the order of Dimension elements given above)
P1,P2,P3
P2,P4,P5
P5
Now we have a requirement to get all the elements from Dimension Dim, which has P2 in its attribute, so in this example A and B would be the expected result. Could you please let me know if its possible?
Thanks.
Re: Filter by Attribute
Posted: Tue Dec 01, 2015 11:14 am
by Wim Gielis
For example:
Code: Select all
{FILTER( {TM1SUBSETALL( [Dimension] )}, Instr([Dimension].[Attribute], "P2") > 0 )}
Combine this with function like LCase and UCase because Instr is case-sensitive.
Also, make sure that finding P2 with Instr does not lead to let's say P25, which contains P2.
Re: Filter by Attribute
Posted: Tue Dec 08, 2015 8:14 am
by Chuks
Hi Again,
Thanks Wim, it worked!
I would like to know if there is a possibility to create MDX query which does a filter by attribute on leaf level elements of the dimension and give us the leaf elements which matches the attribute value along with the parent of that leaf element.
Eg:
Dim1
Alpha(parent of below elements)
A
B
C
Attributes ( in the order of Dimension elements given above)
P1,P2,P3
P2,P4,P5
P5
MDX Query: Filter by attribute which has value as P2.
Expected Result:
Alpha
A
B
Thanks!
Re: Filter by Attribute
Posted: Tue Dec 08, 2015 5:44 pm
by jpm_de
Hi there,
To start working with TM1's MDX functionality, you should take a look at this document:
Creating Dynamic Subsets in Applix TM1 with MDX - A Primer
http://bihints.com/book/export/html/68
And keep in mind, that you can do a lot of stuff with TM1, so there might be other ways (than the one's in other products) to achieve a task more easily.
Therefore is is of much help to describe the why you want to do something and not just the what your next step might look like.
Best regards,
JP
Re: Filter by Attribute
Posted: Tue Dec 15, 2015 2:42 pm
by Chuks
Hi JP,
Thanks for the link! It has lot of useful functions listed. But actually I couldn't get any function from there to use for the requirement i stated below,
I would need a subset which displays both leaf and consolidation of that leaf based on the filter on the leaf elements. Please let me know if it can be created using an MDX expression.
Thanks!
Re: Filter by Attribute
Posted: Tue Dec 15, 2015 4:06 pm
by jduplessis
Chuks wrote:Hi JP,
Thanks for the link! It has lot of useful functions listed. But actually I couldn't get any function from there to use for the requirement i stated below,
I would need a subset which displays both leaf and consolidation of that leaf based on the filter on the leaf elements. Please let me know if it can be created using an MDX expression.
Thanks!
You can use a function like generate. If what Wim provided worked for leaves try:
Code: Select all
{GENERATE(
{ FILTER( {TM1SUBSETALL( [Dimension] )}, Instr([Dimension].[Attribute], "P2") > 0 ) },
{ [Dimension].CurrentMember.Parent, [Dimension].CurrentMember }
)}
Not sure this is the best way to do this, sure someone will correct me if its not.