how to calculate with exception aggregation?

[former_member189599]

I need to make the following request by the customer.

In the report, one column (“accomplished Order”) should be marked with 1 if the requested quantity of a material is less than or equal to the stock and 0 if not. Also, the material is assigned to a "segment".

For instance:

Material	Segment
1000003	S1
1000004	S1
1000005	S2
1000006	S3

Therefore, I must point out in another column (“accomplished Segment”) of the report if the segment meets or fails to meet with the above condition (1 = meets / 0 = not satisfied). If the segment does not meet at least one material of a segment, the entire segment must display 0.

  Here I describe a layout of the report to make it clearer.

Document	Segment	RequestedQuantity	avalaible Stock	Delivered quantIty	Accomplished order	accomplished segment
Doc. 1	S1	4	10	4	1=Yes (4/10)	1 (yes)
	S1	2	2	2	1=Yes(2/2)
	S1	3	3	2	1=Yes  (3/3)
	S2	1	0	0	0=No (1/0)	0 (no)
	S2	2	2	2	1=Yes (2/2)
	S3	1	1	1	yes (1/1)	1 (yes)
	S4	4	6	4	yes (4/6)	1 (yes)
Doc. 2	S1	1	9	1	yes(1/9)	1 (yes)
	S2	4	3	3	No (4/3)	0 (no)
	S2	3	3	3	yes(3/3)
	S2	2	1	0	No (2/1)
	S4	1	1	1	yes	0 (no)
	S4	2	0	0	No
Doc. 3	S1	1	1	1	yes(1/1)	1 (yes)

I tried with a formula with an exception aggregation. The selected aggregation was “Min” and the reference characteristic  was “ZSEGMENT” (an infoobject that takes the value from the material atributtes), but it didn’t work.

I think it is difficult to calculate the indicator on the infoprovider since the user can filter the report on a date range, so the “accomplished segment” can vary according to the selected date.

So, can you give me an idea to calculate de Accomplished segment, please?

Thanks in advance

LUCAS

[Former Member · Accepted solution]

Hi Lucas,

I must point out in another column (“accomplished Segment”) of the report if the segment meets or fails to meet with the above condition (1 = meets / 0 = not satisfied).

Create a new formula (Accomplished Segment) like this

( Requested Qty <= Stock ) * 1 + ( Requested Qty > Stock ) * 0

If the segment does not meet at least one material of a segment, the entire segment must display 0.

this is not clear , are you doing a lookup to check the segments in your target and material master? please elaborate.

[former_member189599 · Accepted solution]

Hi Jyothi

Thanks for the formula to the column "Accomplished order".

About "Accomplished Segment":

I don't doing a lookup to check the segments in your target and material master. I'm calculate on query

Please look at the following table and make the following analysis.

Document = Doc. 1 / Segment = S1 => All materials accomplished order, so "Acoomplished segment" = Yes (1)

Document = Doc. 1 / Segment = S2 => One material not accomplished order, so "Acoomplished segment" = No (0)

Document = Doc. 1 / Segment = S3 => All materials accomplished order, so "Acoomplished segment" = Yes (1)

Document = Doc. 2 / Segment = S2 => One material not accomplished order, so "Acoomplished segment" = No (0)

Document = Doc. 2 / Segment = S4 => One material not accomplished order, so "Acoomplished segment" = No (0)

Document	Segment	Requested Quantity	Avalaible     Stock	Delivered quantity	Accomplished order	Accomplished segment
Doc. 1	S1	4	10	4	Yes  (4/10)	1 (Yes)
	S1	2	2	2	Yes  (2/2)
	S1	3	3	2	Yes  (3/3)
	S2	1	0	0	No	0   (no)
	S2	2	2	2	Yes
	S3	1	1	1	Yes	1 (Yes)
	S4	4	4	4	Yes	1 (Yes)
Doc. 2	S1	1	1	1	Yes   (1/1)	1 (Yes)
	S2	4	3	3	No (4/3)	0   (no)
	S2	3	3	3	Yes   (3/3)
	S2	2	1	0	No (2/1)
	S4	1	1	1	Yes	0   (no)
	S4	2	0	0	No
Doc. 3	S1	1	1	1	Yes (1/1)	1 (Yes)

I hope I have been clearer.

Thanks

LUCAS

[Former Member · Accepted solution]

Hi Lucas,

Create a forumla (ex : Accomplished order1 ) , drag and drop previously create formula Accomplished order and from calculations tab -->choose "minimum" from calculate single value as. you can hide this column in report.

create another formula (ex: accomplished segment) , and write the logic like this

(accomplished order1 > 0 ) * 1 + (accomplished order1 == 0) * 0.

[former_member189599 · Accepted solution]

Hi Jyothi!

I did it, but didn't worked.

I'm going to paste the images. Sorry for not translate all words, but I suppose it's enough that you can understand

1. Formula for Accomplished Order

    

2. Formula for Acoomplised Order1

2.1 Key Figure

2.2 calculate single value as Min

    

3. Formula for Accomplished Segment

    

4. Result Query

    

In this case, all "Accomplished Segment" must be zero, because in each segment at least one material don't "accomplised order".
Can you see?

Also the indicator "Accomplished Order1" seems doesn't calculate good. Because in the following example should be "1" and it is "0".

Thanks.

LUCAS

[Former Member · Accepted solution]

Hi Lucas,

I see from the above image, you are suppressing the result rows of 'segment' and 'material' here. Please change the result to always display and see if your formulas are working as expected or not.

[former_member189599 · Accepted solution]

Hi Jyothi.

First at all, thanks for yours quick answers.

I changed the result to "always display" and see if the formulas are working as expected or not.
Seems that are not working.
Please, look at the lines of results remarked with red rectangle.
For the Segment "S1" seems working ok, but not for Segment "S2".
The "S2" the "Accomplished Segment" should be zero because material 1007937 has "Accomplished Order" = 0.
The "S3" it is equal to "S2" only change the material 1000300.

I really appreciate your help.
Thanks

LUCAS

[Former Member · Accepted solution]

Hi Lucas,

I just figured, accomplished order1 suits your accomplished segment requirement? isn't it? All segments (z01, Z02, Z03, Z04) are zero and other not assigned segment is 1.

you can just delete accomplished seg and make use of accomplished order1 by renaming it.

hope its clear.

[former_member189599 · Accepted solution]

Hi Jyothi!!!

It worked, but I have to show at least one output line for the column is the correct value. I assigned that total material and works OK.

Thank you!.

[Loed]

Hi,

As Jyothi said, what do you mean by this?

If the segment does not meet at least one material of a segment, the entire segment must display 0.

Can you post a sample output using your example above?

Regards,

Loed

[former_member189599]

Hi Loed!

I respond on Jyothi message.

Thanks.

LUCAS