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"

       

      MediaPlan:

      -Site

      -Placement Name

      -Planned Cost

      -Actual Spend (calc - see below)

       

       

      DailyPerformance:

      -Date

      -Placement Name

      -Spend

       

      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.

       

      Example:

      Media Plan (Site | Placement Name | Planned Cost)

      SitePlacement NamePlanned Cost
      Website.comP110
      Website.comP2100
      Website.comTOTAL110

       

       

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

      SitePlacement NameSpend
      Website.comP120
      Website.comP250

       

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

      SitePlacement NamePlanned CostSpend (from Daily Performance)Actual Cost (calc)
      Website.comP1102010
      Website.comP21005050
      Website.comTotal1107060

       

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

      SitePlanned CostSpend (from Daily Performance)Actual Cost (calc)
      Website.com1107070

       

      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".