Hi Irfan,

with your query you are trying to calculate the average of the average values which you calculate in the PU_AVG. But my understanding is that you want to calculate the average per material as written above; but instead of taking all PO items into account, you want to restrict the PU_AVG calculation to the 10 latest PO Items for that material.

If this is correct, then I would suggest to use INDEX_ORDER inside the PU_AVG filter to restrict the values you are aggregating. Basic idea is that you index all PO items based on their creation date and take all where the index is smaller or equal 10.

PU_AVG (

DOMAIN_TABLE ( "EKPO"."MATNR" ) ,

DAYS_BETWEEN (

PU_FIRST (

"EKPO" ,

"_CEL_P2P_ACTIVITIES"."EVENTTIME" ,

"_CEL_P2P_ACTIVITIES"."ACTIVITY_EN" = 'Create Purchase Order Item'

) ,

PU_FIRST (

"EKPO" ,

"_CEL_P2P_ACTIVITIES"."EVENTTIME" ,

"_CEL_P2P_ACTIVITIES"."ACTIVITY_EN" = 'Record Goods Receipt'

)

),

INDEX_ORDER( "EKPO"."CASE_KEY", ORDER BY ( PU_FIRST (

"EKPO" ,

"_CEL_P2P_ACTIVITIES"."EVENTTIME" ,

"_CEL_P2P_ACTIVITIES"."ACTIVITY_EN" = 'Create Purchase Order Item'

) DESC ), PARTITION BY ("EKPO"."MATNR") ) <=10

)

Hope that helps!

Best,

David

Hi David,

Your understanding was entirely correct and your solution works nicely!

As expected from the PQL Guru himself 😉

Thank you so much!

Cheers

Irfan