TM1 Forum

Hallo everybody,

I have developed an mdx statement, that should give me all products, that were sold more than 50 times per month within the year 2010.
So the Year-Dim only contains 2010 and the Periode-Dim contains Total,Jan,Feb,Mar,...,Dec. The Subset "dyn_L0" selects only the N-ELements of the Dim Periode, so it provides months.

My problem is that the statement below gives me all products that have unit>50 in Total of the periode, not for the months but for the whole year. It seems that the statement TM1SubsetToSet([Periode], "dyn_L0").CurrentMember does not work. What is wrong?

{FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
[DB].([Customer].[Total],[Organisation].[Germany],[Organisation IC].[external],[Currency].[LC],[Year].[2010],TM1SubsetToSet([Periode], "dyn_L0").CurrentMember,[DB Measure].[Unit],[Version].[PLAN]) > 50)}

Thank you for any help,
Tobias

Could you write a separate statement for each month and then use the UNION function to join the results together?

Thank you for your answer.

Yes, that would be a possible solution, because there are only 12 months, so I have 12 statements.
But what if I want to see this on a daily basis?
Is there no way to loop through each element? I thought that the CurrentMember function was the right one, but it seems that the statement cannot handle a set of elements, only one element.

A FILTER-functioned MDX query will only work when you specify a single element in all the other dimensions that exist in the cube that has the values you are filtering on. At least that has been my experience. I once spent the better part of a week trying to do something like what you want and could never get it to work regardless of what I did. You could always add a rule calculated element for the 2010 node (maybe in the measures dimension) that looked at each month and resulted in "1" if the total units sold in each individual month exceeded 50 and "0" if any month was less than 50. Then your MDX could filter on that.