2 Replies Latest reply on Jan 26, 2018 5:14 AM by Robert Hyatt

# Calculated field - Sum only if dates match

I'll try to explain this as best I can, unfortunately I can't attach a workbook (sensitive data) and the sample database doesn't have the necessary fields for me to use for this example.

So here's what I have, 2 tables, one is digital ad banners and one is customer clicks on those banners.  I have a MONTH(Activity Date) in the column shelf, TYPE of customer in the rows shelf, and I want to calculate click rate (number of customers who clicked divided by number of customers who saw the banner) for each TYPE by month BUT only if the banner launched that month.

So if you imagine TYPE A has a row with values in Oct'17, Nov'17, Dec'17.  If BANNER X launched in Nov'17, and got views and clicks in Nov'17 and Dec'17, i only want to calculate the click rate for it in the Nov'17 column.  Hopefully that makes sense.

I have been playing around with a calculated field, but I can't get it to work.  When I try the below formula I get the error 'Can't mix aggregate and non-aggregate comparisons or results in IF expression'.

IF MONTH([Publish Date])= MONTH([ActivityDate])

AND YEAR([Publish Date])= YEAR([Activity Date])

THEN SUM([Unique Clicks])/SUM([Unique Views])

ELSE Null

END

• ###### 1. Re: Calculated field - Sum only if dates match

Robert,

In you formula, your dates are not aggregated but the unique clicks are.

I suggest something like;

SUM(if datediff('month', [Publish Date],[Activity Date]) = 0 then [Unique Clicks] end)

/

SUM(if datediff('month', [Publish Date],[Activity Date]) = 0 then [Unique Views] end)

This way you apply the Date logic on every row and then you sum the rows returned by this logic.

note: the datediff with 'month' arg will give you the difference in month between 2 dates.

Michel

• ###### 2. Re: Calculated field - Sum only if dates match

I found a more convoluted way to do it with three separate calculated fields... but I like your way much better Michel! Thanks!!