Trying to Sum expression that's interpreted over all Ids by DateAdd

[#1]

I am trying to create a dashboard the shows the daily marketing spending.  We have about 50 different marketing campaigns, each with a different cost.  I started by building a table where I can assign a dollar value for each type of lead. Then I built a table that looks at each lead, evaluates its campaign, and lists the dollar amount spent.  If I export this tabulated data into excel, it’s very easy to build a pivot table that adds up the total marketing expenditure for each day, but I cannot seem to get it to work in Qlik.  The best I can do is getting the total of rows to show up, but that doesn’t help with the chart I am trying to build.

The chart uses date added as the dimension, and the y axis would be a summation of the expression used to calculate spending. If I leave it broken up by campaign it works fine.

Basically I am trying to subtotals of total spending by day.

It may help to see the expression I am using to calculate spending.  I also have an equally functional version of the code that consists of individual if/then statements joined by +’s.

If(Campaign='MIAA01 | Bankrate | PPC',
count(DISTINCT Id)*[Bankrate, PPC],
If(Campaign='MIAA01 | WXYZ | Detroit',
count(DISTINCT Id)*WXYZ,
If(Campaign='MIAA01 | VC Dynamics | HARP',
Count(DISTINCT Id)*[VC Dynamics],
If(Campaign='MIAA01 | Free Rate Update | Default'
AND [Property State]<>'MI',
Count(DISTINCT Id)*[Free Rate, Non MI],
If(Campaign='MIAA01 | Free Rate Update | Default'
AND [Property State]='MI',
Count(DISTINCT Id)*[Free Rate, MI],
If(Campaign='MIAA01 | Leads123 | Default',
count(DISTINCT Id)*[Leads123],
If(Campaign='MIAA01 | Broker Match | Default',
count(DISTINCT Id)*[Broker Match, Default],
If(Campaign='MIAA01 | Derian | A',
count(DISTINCT Id)*Derian,
If(Campaign='MIAA01 | Equifax | Trigger Leads',
Count(DISTINCT Id)*[Equifax, Trigger Leads],
If(Campaign='MIAA01 | Informa | Default',
Count(DISTINCT Id)*[informa],
If(Campaign='MIAA01 | getpreapproved.com | Default',
Count(DISTINCT Id)*[getpreapproved],
If(Campaign='MIAA01 | LeadPoint | Data Leads',
Count(DISTINCT Id)*[Lead Point, Data Leads],
If(Campaign='MIAA01 | Yodle | Austin Texas',
Count(DISTINCT Id)*[Yodle],
If(Campaign='MIAA01 | Datalot | Data Leads',
count(DISTINCT Id)*[Datalot, Data Leads],
If(Campaign='MIAA01 | Datalot | Warm Transfers',
count(DISTINCT Id)*[Datalot, Warm Transfers],
If(Campaign='MIAA01 | Double Positive | Warm Transfers',
count(DISTINCT Id)*[Double Positive, Warm Transfers],
If(Campaign='MIAA01 | Facebook | Default',
count(DISTINCT Id)*[Facebook, Default],
If(Campaign='MIAA01 | GSF | Call-in',
count(DISTINCT Id)*[GSF, Call-in],
If(Campaign='MIAA01 | GSF | Old Leads',
count(DISTINCT Id)*[GSF, Old Leads],
If(Campaign='MIAA01 | GSF | Referral',
count(DISTINCT Id)*[GSF, Referral],
If(Campaign='MIAA01 | GSF | Self Generated',
count(DISTINCT Id)*[GSF, Self Generated],
If(Campaign='MIAA01 | GSF | Website Traffic',
count(DISTINCT Id)*[GSF, Website Traffic],
If(Campaign='MIAA01 | Lending Tree | Short Form'
AND Purpose='Refinance',
count(DISTINCT Id)*[Lending Tree, Refi],
If(Campaign='MIAA01 | Lending Tree | Short Form'
AND Purpose='Purchase',
count(DISTINCT Id)*[Lending Tree, Purchase],
If(Campaign='MIAA01 | Lending Tree | Short Form'
AND Purpose='MISC',
count(DISTINCT Id)*(([Lending Tree, Refi]+[Lending Tree, Purchase])/2),
If(Campaign='MIAA01 | Lending Tree | Unknown',
count(DISTINCT Id)*(([Lending Tree, Refi]+[Lending Tree, Purchase])/2),
If(Campaign='MIAA01 | Loan Bright | A',
count(DISTINCT Id)*[Loan Bright, A],
If(Campaign='MIAA01 | Loan Bright | B',
count(DISTINCT Id)*[Loan Bright, B],
If(Campaign='MIAA01 | Loan Bright | Stephen Marrs',
Count(DISTINCT Id)*(([Loan Bright, A]+[Loan Bright, B])/2),
If(Campaign='MIAA01 | Marketplace Homes | Default',
count(DISTINCT Id)*[Marketplace Homes, Default],
If(Campaign='MIAA01 | Quinstreet | Data Leads',
count(DISTINCT Id)*[Quinstreet, Data Leads],
If(Campaign='MIAA01 | Really Great Rate | Default'
AND Purpose='Purchase',
count(DISTINCT Id)*[Really Great Rate, Purchase],
If(Campaign='MIAA01 | Really Great Rate | Default'
AND Purpose='Refinance',
count(DISTINCT Id)*[Really Great Rate, Refi],
If(Campaign='MIAA01 | Really Great Rate | Default'
AND Purpose='MISC',
count(DISTINCT Id)*(([Really Great Rate, Refi]+[Really Great Rate, Purchase])/2),
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Purchase',
count(DISTINCT Id)*[Smart Quote Purchase],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='MN',
count(DISTINCT Id)*[Smart Quote Refi, MN],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='IN',
count(DISTINCT Id)*[Smart Quote Refi, IN OR MI WA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='OR',
count(DISTINCT Id)*[Smart Quote Refi, IN OR MI WA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='MI',
count(DISTINCT Id)*[Smart Quote Refi, IN OR MI WA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='WA',
count(DISTINCT Id)*[Smart Quote Refi, IN OR MI WA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='FL',
count(DISTINCT Id)*[Smart Quote Refi, FL VA CT IL],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='VA',
count(DISTINCT Id)*[Smart Quote Refi, FL VA CT IL],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='CT',
count(DISTINCT Id)*[Smart Quote Refi, FL VA CT IL],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='IL',
count(DISTINCT Id)*[Smart Quote Refi, FL VA CT IL],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='TX',
count(DISTINCT Id)*[Smart Quote Refi, TX MD NJ PA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='MD',
count(DISTINCT Id)*[Smart Quote Refi, TX MD NJ PA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='NJ',
count(DISTINCT Id)*[Smart Quote Refi, TX MD NJ PA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='Refinance'
AND [Property State]='PA',
count(DISTINCT Id)*[Smart Quote Refi, TX MD NJ PA],
If(Campaign='MIAA01 | Smart Quote | Default'
AND Purpose='MISC',
count(DISTINCT Id)*
(([Smart Quote Purchase]+[Smart Quote Refi, FL VA CT IL]+
[Smart Quote Refi, IN OR MI WA]+[Smart Quote Refi, MN]+[Smart Quote Refi, TX MD NJ PA])
/5),0)
))))))))))))))))))))))))))))))))))))))))))))))))