Count the values of a characteristic

[Former Member]

Hi Experts,

I want to create a key figure that counts the number of values of a characteristic in a query.

For example, the characteristic is cost_center. In 2007 there are 3 different cost_center and in 2008 there are 2.

If I select 2007 I want to see the number 3, for 2008 the 2 and for both the 5.

Unfortunately , we still use Analyzer 3.1. I solved the problem for Analyzer 7.0 with a calculated key figure and a formula variable but that doesn't work for Analyzer 3.1

Has anyone got an idea?

Greets, Dirk

Edited by: Dirk Schwarzer on Nov 13, 2008 2:58 PM

[Former Member]

Hi Bob,

sorry, I don't really understand the solution. If I create a new calculated key figure where should I set Aggregation, Exception Aggregation and Reference Characteristic? In the query designer or in the maintenance of the key figure info object (workbench)?

Dirk

[Former Member]

Open the query in the Query Designer. On the left-hand side of the screen under InfoProvider, right-click on Key Figure and choose "New Calculated Key Figure".

After you create the new CKF, then you can add it to your query as a new key figure.

Hope this helps...

Bob

[Former Member]

Hi Bob,

sorry, I don't see 'Aggregation' and 'Exception Aggregation' in the properties of a calculated key. I only see 'Calculate result as..', 'Calculate single values as...', 'Time of calculation'.

Dirk

[Former Member]

If you are creating the new CKF on the left-hand side of the Query Designer screen, under the "InfoProvider" heading, and *NOT* on the right-hand side under the "Rows" heading, then you should see a button at the bottom of the screen labeled "Enhance>>". Click on that button to see the Aggregation and Exception Aggregation fields.

Hope this helps...

Bob

[Former Member]

Hi Bob,

I've got no 'Enhanced-Field'. I've got one in the properties but thats all. It's under Analyzer 3.1?

Dirk

[Former Member]

Yes, it is the "Enhance>>" button at the bottom of the Properties screen that I was referring to.

It has been a while since I worked with 3.1, so my memory of the Query Designer functions is a little fuzzy. However, I seem to recall that you may only be able to set this property when you first create the new CKF. After that, some or all of the fields are grayed out and cannot be changed, or you may not even be able to get to them again.

If someone else out there is still working with 3.1 and can add any more details, feel free to jump in.

Hope this helps...

Bob

[Former Member]

Thanx a lot for your answers!

If I create the InfoObject as described in the paper than I have to set the key figure counter to a constant value

of 1 in the transfer rules.

But afterwards I have to load the datas again, haven't I? If there are millions of datas in the cube thats a problem.

Greets, Dirk

[Former Member]

Dirk,

It depends...

If your InfoCube is being loaded from an ODS, then you would not have to reload the ODS from scratch to add a new key figure. You can create an InfoPackage that only updates the new key figure and run it once for all rows, then reload the InfoCube. Still plenty painful, but not as bad as reloading all the data from the source system(s).

When I suggested this paper's approach earlier, I was thinking that this was the only approach available on 3.1. However, on reviewing the paper again, I noticed that the technique described earlier by Abhijeet works "with BW 3.0 and higher". That technique is much easier to implement and does not require a reload.

Again, that would be to create a new calculated key figure, open the Enhance section, and set Aggregation = 'Summation', Exception Aggregation = 'Counting All Values', and Reference Characteristic = <InfoObject to be counted>.

Hope this helps...

Bob

[Former Member]

There is a white paper titled "How to... Count the occurences of a characteristic relative to one or more other characteristics". It goes through the steps to do this in detail.

Here is a link to this document:

[https://websmp102.sap-ag.de/~form/sapnet?_FRAME=CONTAINER&_OBJECT=011000358700002762432003E]

Hope this helps...

Bob

[Former Member]

I believe the above mentioned example uses a formula Total/Average. It works fine..

There is one more way...

You can create a formula with exception aggregation as "Count Non zero values ...." and give reference characteristic as the characteristics which you want to count.