min exception aggregation

[Former Member]

Hi All,

I need to find a solution for a weird requirement. Simply, I need to find the customers whom bought a list of materials (all)

I could find an easy solutions however stuck at a point.

material infoobject

A

B

Sales infocube	material	quantitiy
customer 1	A	10
customer 1	B	20
customer 2	A	30

I made a MP which include Material infoobject and sales infocube.

by making "Access Type for Result Values property to Master Data" for 0material in bEX, I could get the list of the materials which do not have a quantity. (customer 2 material B)

Multiprovider	material	quantitiy
customer 1	A	10
customer 1	B	20
customer 2	A	30
customer 2	B	0

Once I define an exception aggregation (MIN) on quantity with 0material object and I was expecting to get below result.

Quey result	minumum quantitiy
customer 1	10
customer 2	0

However, I am receving below report output.

Quey result	minumum quantitiy
customer 1	10
customer 2	30

Any idea about this ?

thanks

regards

Berna

[Former Member · Accepted solution]

Hi Berna,

What is customer 1 and 2 here? are they info providers? or data of characteristic info object (ex: customer)?

If it is a info object, then you can try creating a new formula --> Quantity then apply exception aggregation choose minimum and reference char as "customer (info object) " . You can suppress the result of material and see if it works.

[Former Member · Accepted solution]

hi Jyothi,

Customer is comming from transaction data from sales infocube.

regards

Berna

[Former Member · Accepted solution]

Hi Berna,

If the characteristic(customer) is available in the Query level then you can try creating the Formula on Quantity and then define exception aggregation with the customer as ref infoobject.

since you want to have minimum W.R.T customer, suppress the result rows for material.

Let me know it this works

Regards

Vivek

[Former Member · Accepted solution]

ok, you can achieve it by creating  a formula/Calculated Key figure as mentioned in my previous post.

[Former Member · Accepted solution]

Customer will be in the output of report. So, why I am adding it as reference to aggregation, I am not sure. I need to get the minimum of material sold in customer detail. I am achieving it, but it does not give me "zeros" which are not comming from transactional data.

[Former Member · Accepted solution]

Berna,

Customer will be in the output of report. So, why I am adding it as reference to aggregation, I am not sure. I need to get the minimum of material sold in customer detail. I am achieving it,

Total results in query are always aggregated against each characteristics that are defined in rows

considering below example, if both Customer and Material results are displayed , then it displays result (quantity) for each material wise in a customer and also total quantity of a customer (ex: customer 1 total result - 30 and customer 2 total result - 30)

Multiprovider	material	quantitiy
customer 1	A	10
customer 1	B	20
customer 2	A	30
customer 2	B	0

Your requirement is to view the minimum material sold qty in each customer, so we have to change the setting of  standard aggregation by using reference char as :customer.

but it does not give me "zeros" which are not comming from transactional data.

I am not sure if this is a typo, you mean to say Customer 2 is not displaying the minimum quantity as '0' ? if yes, then have you suppress the display result  of Material (from the inf object bex properties -->Display-->Result Rows--> choose 'Always supress')

[Former Member · Accepted solution]

Material will not be in the output, only customer will.

Expected result should be here :

Quey result	minumum quantitiy
customer 1	10
customer 2	0

However, I am receving below report output.

Quey result	minumum quantitiy
customer 1	10
customer 2	30

I also tried your suggestion (changing ref. object to customer), it did not help.

cheers

Berna

[Former Member · Accepted solution]

without the material, report output would be 30 for customer1 as well (if it is only char in the rows). not sure how your query rows are defined.

Anyhow, please add material to rows and hide it (from the inf object bex properties -->Display-->Display as--> No display) and see the report output.

[Former Member · Accepted solution]

not helped.

I am sharing the design and output.maybe it will give a better idea.

[Former Member · Accepted solution]

For the aggregation column, instead of applying exception aggregation, choose 'calculate single value as '-->minimum from calculations tab.

[Former Member · Accepted solution]

tried. did not help

please see all values are zero. However, customer C should show 12 as min.

[Former Member · Accepted solution]

Hi,

yes, since each customer has at least one material with zero quantity. to avoid this, you can create a condition (column (+0) >0 ). this will hide all the materials with zero quantity

And please allow the material result rows as  always display(previously we suppressed it in other scenario). you can still hide the material and allow result on it. then you should be able to get the correct values. 

[Former Member · Accepted solution]

hi Jyothi,

please check the third customer. it does not have a zero record. it has a quantity for each material. That is why, it should show 12 as minimum.

regards

Berna

[Former Member · Accepted solution]

Hi Berna,

yes, I do noticed it, it would be because material result are  suppressed earlier. have you tried enabling  result rows for both customer and material (hiding it from rows but result as display)? and then check the report result? it should work.

[sateesh_kumar1]

Hi Berna ,

Can you try creating a formula on the key figure .

Ex KF1 = KF+0 . Then apply Exception aggregation .

or

Use Minimum / Minimum of 0 and Operand function from Mathematical functions section .

[Former Member]

Hi Sateesh,

can not say I understand " Minimum / Minimum of 0" ??

I already tried first option, but did not help ?

regards

thanks

berna

[Former Member]

Hi Berna,

From your above comments could understand that you have done the exception aggregation on Quantity with ref infobject as 0material. If you expect the minimum of Quantity based on the customer ,define the exception aggregation on Quantity with customer infoobject