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

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