BEX Query Calcultaion

[former_member458281]

Dear Experts,

We are having a problem in calculating in To be delivered column.If any material is issuing 2 or more times for any PO then to be delivered column is being populated as a wrong value. (To be delivered is a simple formula in BEX).

Suppose in the below Table for PO 45000100, Material 10001 is issued in 2 times.First time they issued 50 Qty and second time they issued 200 Qty.Total they issued 250 Qty (They ordered 250 Qty).Here In 3rd row To be delivered column should be populated as a "zero" but it is showing as a "50".

Can some one please guide me how can we achieve this requirement in BEX Query designer.

Awaiting for your valuable reply.

PO Number	PO Line item	Material No	Order QTY	Issued QTY	To be delivered	To be delivered we need
45000100	1	10000	100	100	0	0
45000100	2	10001	250	50	200	200
45000100	2	10001	250	200	50	0
45000100	3	10002	300	300	0	0
45000100	4	10003	450	150	300	300
45000100	4	10003	450	300	150	0

Regards

Shankar.

[Former Member]

Hi Shan,

As colleagues have said, problem is not with "To be delivered", but with "Issued QTY" column.

It looks like "To be delivered" is defined as = "Order QTY" - "Issued QTY", then it works fine.

What you have to do is to make "Issued QTY" aggregated by Item Line, and Single values as cumulative.  Try this, and, in case it not works, you have to play with "aggregation" and with "calculate individual values as". For sure there you'll have the solution to your problem.

Good luck!

[Former Member]

Hi,

Please go through screenshot , there are many revised values of one Doc. No , i required only latest revised value  .

In your case , create formula on issued quantity , define aggregation exception tab take summation & reference characteristic PO Number.

Hope it may help you.

Regards,

Anand Mehrotra.

[umashankar_poojar]

Hi Shankar,

         For me it looks like data related, I suggest you to check why 'Issued Quantity' is not adding values from the second OR third orders (if PO Line Item is same). It should behave same as 'Order QTY'.

If you find an answer for this, then you will be able to fix it. Otherwise you have to model it so that every second time the 'Order Quantity' should be reduced by 'Issued Quantity'.

Thanks,

Umashankar

[Former Member]

Hi Mr. Shankar,

This can be solved at BEx query level by defining sum aggregation on Issued QTY as reference PO Number.

Regards,

Anand Mehrotra.

[anshu_lilhori]

Hi,

I think this type of change shall be done at modeling level.

You issues quantity should be in additive mode rather than in overwrite mode.

I am assuming You have DSO in place which have keyfields

PO Number----PO Line item ---Material no.

Then record no.2nd and 3rd should get merged and give issued qty as 250.

Same thing will happen for last two records.

Hope that helps.

Give a second thought on my approach.

Regards,

AL

[Former Member]

Hi Shan,

Create a dummy key figure (Set the display property to "Hide", so that it will not be displayed in the results.)

The formula for the Key figure will be (Dummy Key Figure = "Order Quantity" - "Issued Quantity")

The "To Be Delivered" Key Figure = "Dummy Key Figure" - "Issued QTY").

Hope this helps.

Thanks

Premnath

[former_member458281]

HI Premnath Viswanathan,

Thanks for your replay.

I am not able to solve my issues by your's replay.

Please could you do need for solving this issue any other different logic.

Regards,

shan