TOPSUM Filter in 9.5.1

[ulla]

Hi,

i'm a bit confused about the functionality of the topsum filter in TM1 Cube Viewer. I have the following Data in my Cube, for example:

Sales
Product A 100.000
Product B 200.000
Product C 300.000
Product D 400.000
Product E 500.000
Product F 600.000
Product G 700.000

Now I do rightclick on Filter, choose Topsum and enter the value 400.000. The resultset I expected was Product D, E, F and G.
But unfortunatly I get only Product G. It seems that tm1 only filters the largest element.

Has anyone experienced the same? Or did I misunderstood the Filter?

Thanks!

[rkaif]

Ulla,

What you are trying to achieve is possible using the View Extract option.

TopSum is used to (read following from the manual):

filters the view to display only the largest elements whose sum is greater than or equal to n, where n is a number specified in the Value option.

Use the View Extract option and in the Range select Greater than or equal to A. In the Value A text box provide the value which you want to see.

Hope it helps.

[ulla]

Hi rkaif,

thanks for your quick answer. The View Extract option is a workaround, but unfortunately not as comfortable as the right click filter option.

Ulla

[paulsimon]

Ulla

While ViewExtract is OK it is not dynamic

You can achieve what you want using MDX

When I record the MDX for a TOPSUM query I get something like this

Code: Select all

{ ORDER( {TOPSUM( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [uk-locs] )}, 0)}, 1000000.000000, [uk-prof].([products].[Koala Beer],[version].[Actual],[profitms].[Units Sold],[months].[Jan]))}, [uk-prof].([products].[Koala Beer],[version].[Actual],[profitms].[Units Sold],[months].[Jan]), BASC) }

According to the manual

TopSum

Filters the subset to return only the largest elements whose sum is greater than or equal to n, where n is a number specified in the Value option.

I think that there is a mistake in the manual and it should say the largest element (singular not plural)

I don't know if you already know, but can see the MDX that is generated by choosing Expression Window on the View menu of the Subset Editor. I edited the expression that I recorded above as follows:

Code: Select all

{ ORDER( {FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [uk-locs] )}, 0)}, [uk-prof].([products].[Koala Beer],[version].[Actual],[profitms].[Units Sold],[months].[Jan]) > 1000000 )}, [uk-prof].([products].[Koala Beer],[version].[Actual],[profitms].[Units Sold],[months].[Jan]), BASC) }

This gets every UK Location that sold more that 1000000 units in January of Product Koala Beer for version Actual. It sorts the result by the same.

In the above the cube is called uk-prof, hence [uk-prof].( specify one element from each dim )

Unfortunately you have to edit the MDX in the Expression Window to get this. There is no way to record it. However once you have typed it in it is dynamic and will change as sales change. In practice it would be more useful at a higher level eg Total Products for Total Year.

Regards


Paul Simon

[ulla]

Thanks a lot for your help