cancel
Showing results for 
Search instead for 
Did you mean: 

Displaying the result of the TOP N query

Former Member
0 Kudos

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!

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Aneesh,

your proposal has solved the problem for displaying result for a single year.

However, I still can't figure out how to display the result for all 3 years in one table.

I have created 3 pre-queries with the sets of materials for different years and corresponding variables. But in the "final" query it doesn't seem to be possible to have key figures, restricted to those variables.

Former Member
0 Kudos

You can also use the ABC classification of the Data Mining Toolset

Former Member
0 Kudos

Irina,

Should be achievable using exception aggregation and pre-query.

Create a query that gives the list of material that account for 80% of the sales using condition on the sales column.

In the second query, using exception aggregation, count the number of materials that make up this set for the year. Create a new calculated key figure with the formula as qual to sales key figure. In the properties window, press the enhance button and select count of all values in the first drop down and material in the second drop down. Filter the query on material with a characteristic variable with processing as replacement path. In the variable point it to the first query. Drilldown for this will be year.

This will give you number of materials for one year. If you want comparitive for three years, then three pre-queries and three variables with the calculated key figure restricted to a variable pointing to one years query.

You can use pre-calculated value sets to improve the run for the result query.

Cheers

Aneesh