TM1 Forum

Im having a little trouble getting a filter statement to work using the AND operator in a subset.

The idea was to check that data was >0 in one cube AND that data was not null in a different cube. When I combine the statements I get nothing returned. When I use them individually they bring back elements.

I have checked and their are elements who have data > 0 in one cube and have a nothing "" in the other cube. Am I unable to filter elements by looking at different cubes?

I thought it is easy, so did a fast test. I decided to describe it, as the results were a little bit weird for me.
The final result came in point 4f, so you may skip 4c, 4d and 4e.

What I did:
1a. Created dimension dim1 with elements a1, a2, a3.
1b. Created dimension dim2 with element b1.
2. Created cubes c1 and c2 both built of two dimensions: dim1 and dim2.
3. Inserted a value of 1 in the cell(a1,b1) in cube c1 and inserted value of 1 in the cell(a2,b1) in cube c2.
4a. Created dynamic subset that will return only those elements from dimension dim1 that have in cube c1 values of 1 in b1 column.
syntax: {FILTER({TM1SUBSETALL([dim1])}, [c1].([dim2].[b1])=1)},
subset name:onlyc1,
effect: as expected - only a1 element returned in a subset.
4b. Created dynamic subset that will return only those elements from dimension dim1 that have in cube c2 values of 1 in b1 column.
syntax: {FILTER({TM1SUBSETALL([dim1])}, [c2].([dim2].[b1])=1)},
subset name:onlyc2,
effect: as expected - only a2 element returned in a subset.
4c. Created dynamic subset that will return both results by typing this:
syntax: {FILTER({TM1SUBSETALL([dim1])}, [c1].([dim2].[b1])=1), FILTER({TM1SUBSETALL([dim1])}, [c2].([dim2].[b1])=1)},
effect: NOT as expected - two a2 elements returned in a subset.
4d. Created dynamic subset that will return both results but in different order than expected in 4c by typing this:
syntax: {FILTER({TM1SUBSETALL([dim1])}, [c2].([dim2].[b1])=1), FILTER({TM1SUBSETALL([dim1])}, [c1].([dim2].[b1])=1)},
effect: NOT as expected - two a1 elements returned in a subset.
4e. Created dynamic subset that will combine a static element and a dynamic one by typing this:
syntax: {[dim1].[a1], FILTER({TM1SUBSETALL([dim1])}, [c2].([dim2].[b1])=1)},
effect: as expected - elements a1 and a2 in a correct order.
4f. Tried to use different syntax that calls two already defined subsets by typing this:
syntax: {[dim1].[onlyc1], [dim1].[onlyc2]},
effect: as expected - elements a1 and a2 in a correct order - spectacular success!

As I suspect here a bug here (cases 4c and 4d) I should say I tested it on 9.5.1.

HTH

I am sort of following what you are saying here.

The issue I have though is that I want to filter Dim1

where Cube1 (which includes dim1)>0 AND where Cube2 (which includes dim1)="".

I only want elements where both statements return as true.

If element 1 has a value >0 in Cube1 and has no value in Cube2 then it should be returned.

Your example doesn't appear to show where two boolean statements evaluate to TRUE for a single element. Have I missed something?

PlanningDev wrote:Your example doesn't appear to show where two boolean statements evaluate to TRUE for a single element. Have I missed something?

I don't believe there is any such thing as an "AND" in MDX. It's not SQL with a WHERE clause but If you embed a FILTER statement inside another FILTER statement isn't that the same thing as an AND? That's what he did in example 4c. It's just that he said the results weren't what he expected. Filtering the common dimension for items that have the values you want in Cube1 and then filtering that result set for items that have the value you want in Cube2 should do the trick. The syntax may be tricky.

It does look like AND is a valid MDX operator. The question is wether or not the filter function will use. Also Im trying to filter on a numeric value in one statment and a picklist in the other. Not sure if filter works with strings (picklists) differently

I should be reading with more understanding. Yes, you wrote about AND and I did an OR example.

Anyway, there is an "INTERSECT" function that might do what you need. I am not able to check it on the example I described above, but I think one or other way you should be able to make it work.

Here is a quotation from the MDX_Primier.doc I often use where it comes to MDX (source: http://forums.olapforums.com/viewtopic. ... =547#p1550).

Intersect

Intersect returns only members that appear in both of two sets. One example might be to show products that performed well both last year and this year, or customers that are both high volume and high margin. The default is to drop duplicates although “, ALL” can be added if these are required.

This example returns leaf Product members that have an Amount > 5 as well as a Count > 5.
{
INTERSECT(
FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]) > 5 ) ,
FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Count]) > 5 )
)
}

I tried intersect and no luck.

It may be that the I'm trying to evaluate data in two different cubes to come up with a single set of members from the dimension.

My filter would have to look like.


{
INTERSECT(
FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test].([Posting Measures].[Amount]) > 5 ) ,
FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, [Test2].([Posting Measures].[Count]) > 5 )
)
}

Did you try using INTERSECT on two already created subsets?
Like the example from the 4f?

It would be something like:

{INTERSECT(
[Product].[Subset_Based_On_Values_In_Cube_Test],
[Product].[Subset_Based_On_Values_In_Cube_Test2])}

?