I have a table of 49k rows. It shows all order lines with Part id, qty (per line item), the fiscal year for each line item and total order value for that line (qty*unit price).
I need to find a way to show by fiscal year, the part id (one time) its total fy07 qty sold, total fy order volume and then beside that show the following fiscal year information and see if it decreased in unit price or increased.
(the data is by line items)
Fiscal Year Product Code Part ID Order Qty Order Value
2007 200 ABC 5 23.50
2008 200 ABC 5 25.00
Overall what I need to show off to the right side of all of this information is a summary of Part ID like this
FY07 Order Qty FY07 Order Volume Fy07 Avg Price FY08 Order Qty FY08 Order Volume FY08 Avg Price Increase/Decrease. Essentially above it would've shown that the Fy07 price was 4.70 and the Fy08 price was 5.00 and increased by 6%. The thing is not to let the part id repeat itself 200 times because its line items from orders and it needs to be grouped by part id and also that part id qty sold per year and overall order volume per that part id for that year and then compared.
Any suggestions or did I make sense?