Average value of a material

[Former Member]

Hi All,

I am writing a query where I have to calculate the average price of each material.

Example, Material M1 can have many vendors, say,V1 and V2

I have to calculate for M1 =

(Price * Qnty)for V1 + (Price* Qnty)for V2...../Total Quantity for (V1V2+....)

Likewise I have to do for all the materials

I have taken in rows, material and then vendor.

In columns I have the price and qnty of all the materials for respective vendors.

How can I do the above calculation of average?

Thanks and regards,

SB

Message was edited by:

Sharmishtha Biswas

[Former Member · Accepted solution]

Hi,

Try this:

1) Take Material and then Vendor in to the orws

2) Create a Global Calculated KF with Price * Qnty as "Before aggregation" as aggregation property.

3) Take the Global Kf in to columns area and also Price and Qty kfs.

4) Create a Local Calculated KF like SUMCT <Qty> .Lets say this as LKF1

5) Create another Local Calculated KF like SUMCT <Global Calulated Kf created by step 2>.Lets say this as LKF2.

6) Create a formula column like LKF1/LKF2.

It is just a try .Wait for experts answers.

with rgds,

Anil Kumar Sharma .P

[Former Member · Accepted solution]

Hi Anil..

Thanks for the suggestion.

I'll try and get back to you.

Regards,

Sharmishtha

[Former Member · Accepted solution]

Hi Anil,

I created the global calculated figure.

But when I bring in the colmns and see the query result, it shows all Xs and DBZ.

No value comes.

whereas if I do it locally, value comes.

Pl suggest something.

Thanks,

SB

[Former Member · Accepted solution]

Hi,

<i>But when I bring in the colmns and see the query result, it shows all Xs and DBZ.

No value comes.</i>

Use NODIM function for both operands in the Global KF's formula.

With rgds,

Anil Kumar Sharma .P

[Former Member · Accepted solution]

Hi Anil,

Sorry for the late reply.

But ofcourse I have already used the NODIM function.

The doubt that I mentioned was after I had done that.

The formula at both the places are same. (local and global), both ar with NODIM only.

But the local is working fine and the global is giving wrong output.

Can you pl see some other reason for such a thing happening.

Thanks,

Sharmishtha

[Former Member · Accepted solution]

Hi,

Is ther eany difference of the properties like Aggregation,Formula collision between GCKF and LCKF?

With rgds,

Anil Kumar Sharma .P

[Former Member · Accepted solution]

Anil, I have not used more than one structure, so I believe there should be no formula collision.

There is diffrence in aggregation, as in the CKF I have used before aggregation and in LKF I have used after aggreagtion (by default).

Pl advise.

Thanks,

Sharmishtha

[Former Member]

I think that directly in formula propeties or key figure properties you can set calculate result as average

[Former Member]

Hi Oscar,

Thanks for contributing.

I have been trying to search for such a function for sometime now, but in vain.

Can you pl elaborate.

Regards,

Sharmishtha

[Former Member]

If you drag and drop a KF into your query area in BEX, do click on rigth button an choose properties.

There you have "calculate result as" you can choose average of all values or average of all values <> 0.

[Former Member]

Thanks Oscar,

I am aware of that functionality but in my case it is not workable.

We have used Cognos8 as front end to the SAP BW system. This restricts us from using any display properties in the query because, after getting imported to the Cognos end these display level changes stand null and void.

Regards,

Sharmishtha