TM1 Forum

I’m struggling with a dynamic subset and filtering on two attributes in 3 dimension at the same time.
I have 3 dimension , Account, SalesMan and Category
‘Account’ has two attributes : SPCode,CatCode
‘SalesMan’ has one Attributes : SPCode
‘Category’ has one attributes : CatCode
I am trying with filter, but it’s not working .
{FILTER ({TM1subsetAll ([Accounts] )},(( [Accounts].[SPCode] = [SalesMan].[SPCode])
AND ([Accounts].[CatCode] =[Category].[CatCode])))}

Any body can help me please.

Have you tried recording this in the subset editor?

yes, but not working . Any idea pls.

You can do this very easily without using MDX. Using a while statement in TI loop through the account dimension from 1 to the dimsiz and only insert elements in to the subset if they meet your criteria. Would this work or does the subset have to be more dynamic? For me you could do this every time you update the account dimension or update the attributes on either of the other 2 dimensions. This does however only work if the attributes are being updated via TI and not being done manually. Saying that if you use an excel template to edit the attributes you could add an action button to the sheet,

Jim.

I suspect you will need to look at your use of the AND condition.

What results are you getting from your original query?

I wasn't aware you could reference other dimensions in a dynamic subset expression - never to old to learn I guess:)

It seems a reference to (an attribute of) another dimension without providing a specific element is resolved by taking the first member of that dimension. I.e. [SalesMan].[SPCode] would refer to the value of attribute SPCode for the element with index 1 of the SalesMan dimension - probably not what the OP intended.

Before AND condition it was fine, i.e: Filtering Accounst and SalesMan dimension working fine
{FILTER ({TM1subsetAll ([Accounts] )},(( [Accounts].[SPCode] = [SalesMan].[SPCode]) }
But when adding Second Condtion i.e. Filtering with Category Dimension then it's not working.
AND ([Accounts].[CatCode] =[Category].[CatCode])))

Alan,
Can i have your attention regarding my issue?
Please help me .
Thanks