2 Replies Latest reply: Dec 7, 2011 6:06 PM by John Bizon RSS

    Set Analysis Question?

      Hello all-


      I am trying to calculate a field for a subset of records using data from records that are not selected. I think this can be done using set analysis but I haven't been able to figure it yet. It seems like everything I try creates records in my table for the purchases.


      For each sale in the below table, I am trying to calculate the pro-rata cost as the sum of the purchase costs * quantity for that contract / sum of purchase quantities.  In my example below, I am calculating the Cost for Contract 51 HourEnding14 as (934.67 + 1652.38) * 100 / (53.22 + 95.78)



      Example Data:

      sample data.jpg

      Desired Output:

      desired output.jpg


      Is there away to do this without creating a separate table for the total purchase costs and quantities?


      Thanks for your help -