Page 1 of 1
TopCount based on a sum of two elements
Posted: Thu May 12, 2011 8:42 am
by 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
Re: TopCount based on a sum of two elements
Posted: Mon May 23, 2011 4:54 am
by 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]) )}
Re: TopCount based on a sum of two elements
Posted: Mon May 23, 2011 2:21 pm
by 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
Re: TopCount based on a sum of two elements
Posted: Mon May 23, 2011 4:42 pm
by tomok
Jaromir Flidr wrote: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.
Re: TopCount based on a sum of two elements
Posted: Wed May 25, 2011 10:33 am
by Jaromir Flidr
tomok wrote:
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...
Re: TopCount based on a sum of two elements
Posted: Thu May 26, 2011 2:41 am
by 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?