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

    Calculated field - Sum only if dates match

    Robert Hyatt

      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