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"
-Actual Spend (calc - see below)
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)
|Site||Placement Name||Planned Cost|
Lets assume sum of Daily Performance gives us the following (remember, this is aggreagating over the dates):
With Placement in the field, I get the following for "Actual Cost" (Correct!)
|Site||Placement Name||Planned Cost||Spend (from Daily Performance)||Actual Cost (calc)|
If I pull the placement the placement name in the view, I get:
|Site||Planned Cost||Spend (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".