TopCount based on a sum of two elements

[Jaromir Flidr]

Hello
I'm playing with TopCount mdx subset, and trying to get TopCount based on sum of current and previous year. It seemed straightforward, so I used

Code: Select all

{ TOPCOUNT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, 10, [Test].([Blah].[CurrYear]) +  [Test].([Blah].[PrevYear]))}

and it worked really well up to a point when [Blah].[CurrYear] is zero. In this case the Product is completely ommited regardless of a value of [Blah].[PrevYear]

Is there a workaround for this?

many many many thanks
JF

[Oratia623]

Have you tried to use a consolidated element instead of adding them up within the MDX? ie

Code: Select all

{ TOPCOUNT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)}, 10, [Test].([Blah].[Last Two Years]) )}

[Jaromir Flidr]

Well, I belive consolidating the elements within the dimension would work, however I would rather not pollute the nice and easy dimension with such consolidation....
In any case thanks for your reply, I would take this as a last resort workaround

[tomok]

and it worked really well up to a point when [Blah].[CurrYear] is zero. In this case the Product is completely ommited regardless of a value of [Blah].[PrevYear]

Your syntax is invalid, you can't do a TOPCOUNT based on adding two nodes together using a plus sign in the MDX. My guess is you were tricked into thinking it was working because the TOPCOUNT for [Blah].[Curryear] probably returned the same set as what you were expecting to see when adding CurrYear and PrevYear. The only solution is to create a consolidation of the two years in the dimension and use that node in the MDX.

[Jaromir Flidr]

Your syntax is invalid, you can't do a TOPCOUNT based on adding two nodes together using a plus sign in the MDX. My guess is you were tricked into thinking it was working because the TOPCOUNT for [Blah].[Curryear] probably returned the same set as what you were expecting to see when adding CurrYear and PrevYear. The only solution is to create a consolidation of the two years in the dimension and use that node in the MDX.

Tomok: thanks for your reply. Well I'm beginner on MDX, so you might be right. Just thinking if my syntax is invalid, I should get an error message and not (a wrong) result...

[Gregor Koch]

Hi
I also think that in your example a consolidation would be the way to go.

But, after reading the thread and just out of curiosity I have tested this and for me this works just fine (definitely not being tricked here) and I don't see how the addition of two values would be and invalid numeric expression. I have also tried to give one of the 'products' zero values in either of the two measures with very high values in the counterpart, still works and results are correct.
Obviously if both values return zero for all products the result will be a pretty much random list of products.

@ Jaromir: Are you sure that only [Blah].[Curr Year] is zero for the omitted product.
Is [Blah].[PrevYear] maybe a rule calculated value (not that it would make much sense for a prev year value) that is not fed?

@Tomok: How is using a '+' not returning a valid numeric expression?