Hello,

I categorize my inventory items by several overlapping classes. I’m trying to execute a summary search where I have the combined months of supply for each class of items. Months of supply is defined as the ratio between consumption per month and current quantity on hand.

I’m using Oracle’s analytic function to sum the total consumption over each class THEN divide by the sum of the quantity on hand for the items in each class. What I get is a result as shown in the attached file.

When I try and take the average on each field, grouping by class, I get an unexplained error with a message to contact support.

This is the current formula I’m using:

ROUND(sum /* comment */({custitem_rm_quantityonhand} + {custitem_rm_committed})OVER(PARTITION BY {custitem_sww})/NULLIF(ROUND(sum /* comment */({custitem_total_quant_sold}*{weight}*{custitem_pa perbasisweight}/NULLIF({custitem_finishedproductbasisweight},0)) OVER(PARTITION BY {custitem_sww})/12,0),0),2)

This is the skeleton structure that may be easier to digest:

SUM /* comment */({a}) OVER(PARTITION BY {c})/

NULLIF(SUM /* comment */({b}) OVER(PARTITION BY {c}),0)

Has anyone had any luck using Oracle’s Analytic Function in a summary search? An even better question to ask, is there an easier way to achieve my goal?

Thanks,

Andrew
This is a cached copy. Click here to see the original post.