Skip to Content
0
Former Member
Aug 08, 2005 at 12:53 PM

Displaying the result of the TOP N query

17 Views

Hi all,

we are facing a challenging task were we are going to build a query that gives the answer on the question:

How many different materials (of the top sold) represent 80% of the total sales in quantity?

The result should of the query should be very short:

2002 2003 2004

Nr of materials

reps 80% 432 453 499

The query is build on a CO-PA based cube, where each line corresponds to a billing item with material and quantity.

By using a Top 80% condition on the quantity sold keyfigure we get out the top 80% materials sold:

Year Material QTYSold QTYSold-Ranked

2002 Mtr0323 10000 1

Mtr0262 9000 2

Mtr0243 8700 3

... ... ...

Mtr0763 600 423

Result 932000 423

2003 Mtr0123 9700 1

Mtr0332 9100 2

Mtr0243 8700 3

... ... ...

Mtr0763 543 453

Result 912000 453

We would like to supress the materials and only show the result row (or the raked position of the last material).

The problem is that the 'Calculate Single Value As' properties of a formula (or keyfigure) only applies on Displayed Data values. All tests we have done so far with 'supression of zeros' & 'always hide' has messed up or counting / ranking functions.

Is there any function in within formulas that have same functionality like Count,Rank etc?

Is there any other way to supress materials and only show results?

Any suggestions are welcome!