data comparison on multiple columns

[vireshkolagimat]

Hi All,

Please find the attached sample where i have mentioned the input and output data.

For orderno 1106200001 the sum(orderedqtyactual) should be 11 but i am getting 22 since it is summing the data for both order and return. I need to show data for order since the complete order got returned.

I tried to use the set analysis to filter based on ORDER_TYPE but if i have any orders with only return as ORDER_TYPE then i will miss that order no.

Let me know for any clarifications.

Regards,

VK

[YoussefBelloum]

Hi,

If you need to show data for orders that have been returned, you can make calculation for Orderno that have a "RETURN" line:

Sum( {<ORDER_TYPE={'RETURN'}>}  orderedqtyactual)

[vireshkolagimat]

Hi, Thanks for  suggestion but if i try above method, i loose the qty against the Order. Basically i am showing how many qty was ordered and how many delivered.  Here the problem is we have the same orderno for both order and return.

[YoussefBelloum]

you can try this on your output table:

dimensions: ORDERNO, LINE_NO

Expression: aggr(sum(ORDERQTYACTUAL),ORDERNO,LINE_NO,ORDER_TYPE)

[vireshkolagimat]

Hi, yeah it is working fine but what if i have duplicate line_no? can i make use of distinct to get unique Line_no.

Thank you.

[YoussefBelloum]

you have already duplicate Line_no but with different order_type right ?

try with more data and see what you have as an output.

[vireshkolagimat]

Hi, will get back to  you. Thanks