Problem in counting/aggregating key figure in query result

[Former Member]

Hi,

I have some trouble with the aggregation of key figure values in query results. Let me explain using an example:

In a query result I have the characteristics Supplier, Product and the key figures Order quantity, Nr. of different products. This last key figure is a counter that sometimes results 0.

The result could be as following:

Supplier Product Nr.of diff Products

A A1 1

A A2 1

A A5 0

A A3 1

When I look at the results of the query at this detail level, the result row of Nr. of different Products is 3, excactly as I want it to be. My problem occurs when I remove the drill-down on Product. In this case, I see the following:

Supplier Nr.of diff Products

A 4

As you can see the values 1, 1, 0 and 1 in the drill down are all counted and the result is 4. However, I would like it to just sum up the individual numbers and it to result 3.

I have tried (hard) to adjust my settings of the key figure in the query designer, but I can't solve this problem.

Can you please help me out?

Best regards,

Hans

[GFV]

Did you implemented the solution descibed in "How to … Count the occurrences of a characteristic" (see http://service.sap.com/bw under InfoIndex section), or something else?

Best regards

GFV

[Former Member]

In this case the problem indeed occurs for a counter. Do you think the problem is due to the counter?

[GFV]

How did you implement "No of Diff. Products"?

Is it a Key Figure of the Cube?

Is it a formula based on a counter?

If you need to count WITHOUT the Drill Down on "Products" see the ASAP I suggested ... there you find how to count the occurrecies of Characteristic Value (Products) according to another one (so Supplier)

Hope it helps

GFV

[Former Member]

The "No of different products" is indeed a counter implemented like the ASAP you suggested. For this I use a "Counter" and a "Counter per product".

So the counter should count the number of different products on product-level. In the report however the results are shown on supplier-level, and users should be able to drill down to product-level. This is where the differences occur. On supplier-level the counter shows for example 5, and in the drill down the counter shows 4 (which is caused bij a "0" or "X"). I illustrated this in the example above.

Hope you can help me out.

Hans

Message was edited by: Hans de Klein

[GFV]

May be it's a problem of zeros? I mean are you using "Average of all values" or "Average of all values <> 0"? Using the first one should explain the difference between aggregate (no product display) and detailed level.

If you update the cube by an ODS (from the Change log I mean) it's quite normal as behaviuor having two lines that do sum to 0 ... but you sholud exclude such values.

Hope it helps

GFV

[Former Member]

Using "Average of all values" or "Average of all values <> 0" does work on the product-level but not on the supplier-level. That is, on supplier-level it seems that the number of rows beneath it (in the drill-down) are counted, not the actual values in the rows.

If there is no solution to 'adjust' the 0 in the query results I agree to look how this 0 can be excluded. More specific: how do those 0's appear in the cube (not filled by an ODS) and how to exclude these rows making a selection on certain characteristic values.

Message was edited by: Hans de Klein

[GFV]

I never had other problems but this and can say the ASAP is pretty good. Let me suggest to check the InfoCube content with LISTCUBE without any aggregation criteria (no flag on DB Aggregation, I mean): that's the best way in my opinion to see 0's rows origin ...

About the causes of this phenomenon I can't help you but according to me you should investigate further

Good luck

GFV