6 Replies Latest reply on May 21, 2019 1:15 PM by Jim Dehner

    Cannot mix aggregate and non-aggregate arguments to function

    Douglas Lauw

      Hello everyone,

      I have 2 sets of data, 1 from Excel and 1 from Access. In the Excel data, I'm creating a new field which contains output result by matching two dates, 1 from Excel and 1 from Access.

      Basically I am matching each record whose 'Process Date' (Excel) is equal to the 'Holiday Date' (Access), by the way both fields contain data with date format. When  the two are matched, then it creates an output in which 'Total Order' is subtracted with an 'N' variable.

      In this attempt, I used a simple if statement as follows:


      IF ([ProcessDate] = DATE ("10/14/2013"))then [Total Orders]-[N] else  [Total Orders] end


      The system validates and I get the exact results, however, the date is not simply one date but there are many or hundreds, I tried a new approach by converting the DATE into multiple dates  as per below:


      IF ATTR ([ProcessDate] = (ATTR([International Holiday List].[Plant_closing_date])))then [Total Orders]-[N] else  [Total Orders] end


      Since International Holiday List].[Plant_closing_date has date format, I removed the Date function, however with this approach, I kept getting a "Cannot mix aggregate and non-aggregate arguments to function"

      I am not sure if this is a true error as per above or if a multiple data source issue.


      Thanks for stopping by.


      I'm hoping someone could help me with these challenges.