on 08-08-2005 1:53 PM
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!
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can also use the ABC classification of the Data Mining Toolset
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.