Count Calculation for Sales

[Former Member]

Hello,

My requirement is to take the count of Sales order which are Blocked and the
logic to derive it requires the data to be checked in the Sales Item table.
Join between VBAK and VBAP.

For Eg.
Suppose 1 Sales order has 10 Line Items and 2 are blocked.
Another Sales order has 5 line Items and 3 are blocked
We need to take the values of the blocked Line Items and
show as Blocked Value

Expected Result:
Count of Blocked order = 2

If we take the result at Sales order level(VBELN) things are fine but if we take report at
say Company Code level the values are as follows:

If we make the formula as Before Aggregation the count is coming as 5(2 +3).
and if it is After Aggregation the count is coming as 1 for the Selected Company code.

Is there any way to handle such scenarios?

Regards,
Surya

[rajarshi_muhuri]

one unelegant way would be to have a calculated column which gives a flag value 1 , if any one line item is blocked . unfortunately this IF/case statement  that checks if any of the line items for blocks - would be a ROW intensive operation. So would be an absolute performance killer

[rajarshi_muhuri]

actually , i think this will work

you should move ALL calculations in the calculation layer (and anyway , this should be done for performance reasons anyway), there you have an option of  counter which does an distint count ( on an attribute)

[Former Member]

Hi Surya,

I would handle these type of requirements in the OLAP layer (here it can be BOBJ) where the count can be calculated by any kind of slice/dice or filters. It will not be quite dynamic if you handle it as a calculated/restricted measure in HANA.

/bikas    

[former_member191765]

I think you are using join on aggregation.. I believe Join on aggregations will not work..

Can you try with group by..! if I have not mistaken your query.