0 Replies Latest reply on Sep 26, 2013 10:52 AM by G Scott Stukey

    Blending the aggregate of table B on rows of Table A

    G Scott Stukey

      Hi All,


      This problem has boggled my mind, and while the answer may have been addressed in other forums, I couldn't find the answer I was looking for...


      I have 2 tables, a "Media Plan" and a "Daily Performance"




      -Placement Name

      -Planned Cost

      -Actual Spend (calc - see below)





      -Placement Name



      We have a "Actual Spend" logic that we apply where: if sum(spend is > planned cost, then planned cost, else spend).  My issue is that, when I have the site & placement name in the view, the calculations happen on each placmeent and the totals for each site are correct. Whe I pull the placement name out of the view, the logic applies to the summed values. It sums then calculates, as opposed to calculating then summing.



      Media Plan (Site | Placement Name | Planned Cost)

      SitePlacement NamePlanned Cost



      Lets assume sum of Daily Performance gives us the following (remember, this is aggreagating over the dates):

      SitePlacement NameSpend


      With Placement in the field, I get the following for "Actual Cost" (Correct!)

      SitePlacement NamePlanned CostSpend (from Daily Performance)Actual Cost (calc)


      If I pull the placement the placement name in the view, I get:

      SitePlanned CostSpend (from Daily Performance)Actual Cost (calc)


      I get the calculation done on the aggregate, instead of the aggregate of the calculations.


      Is there a way to get the sum of a column of a 2nd table to be "joined" (for lack of a better term) to each row of the 1st table?


      Anytime I try to create that calculation, I get a "cannot mix aggregate & non-aggregate functions".