HI, I have two files, a shipping file and a budget file.

The shipping file has company, division, and account along with various metrics including shipped quantity.

The budget file is has company, division and budget, no account information.

I am trying to calculate the variance at the division and company breaks based on the budget file (shipped QTY minus Budget QTY).

I join them by company and division.

The issue I am having is either the budget amount is overstated (duplicated by number of account in the division) or does not perform the calculation correctly (It seems grab the last shipped from the last account record rather than the sum of the shipped qty.

I used the following :

 COMPUTE SHIPPED_DIFF/I11 WITH DIVISION = (ACTUAL_SHIPPED + FROZEN_SHIPPED) - BUDGET; NOPRINT 

to calculate the variance and summed them using subfoot/subtotal/recompute/etc. ( I think I tried them all), still no bueno.

I tried 'SET SUMMARY=EXPLICIT', which did not give any better results. I tried to use the recomdations of similar issues on the forum, but I think because I am using a file, it does not function the same way as those issues did.

Any ideas on how to accomplish this?

PS. I know you need to see code...If comes down to that I will post something, but at the moment my is a mess, and secondly its pretty long because this is only a portion of the entire report and I'd have to simplify it so you wont need to look through a bunch of unrelated code.

This message has been edited. Last edited by: Lloyd,

Try:

sum
sum.q1
sum.q2
ave.budget
compute var = ( sum.q1 + sum.q2 ) - ave.budget ;
by company by division

sum q1 q2
by company by division by account

on company recompute subtotal

This message has been edited. Last edited by: j.gross,

Closing this topic, never really solved the this (IMHO), but I got around it by creating summaries for company and division and joined them.

JOIN to sumamry tables is a good solution...