I am trying to create a calculated field which will result in an incremental application amount for two different types of applications (purchase or refinance). I am getting an error in the below code:
if (sum([ROLLUP_PURP_CD]) = "PUR" and sum([control]) = 0)
then sum([Responders])*avg([Inc. Resp. Rate - Purchase])
else sum([Responders])*avg([Inc. Resp. Rate - RFI]) end
ERROR MESSAGE: "Sum is being called with (string), it should be called with (float) or (interger)" and "Cannot mix aggregate and non-aggregate arguments to function"
The other thing I thought might help would be to modify my ([ROLLUP_PURP_CD]) field from being "PUR", "REFI" to a number...but I don't know how how to do that either.
I imported my data in a flat Excel file, so I'm not using any kind of ODBC connection.
If anyone could recommend how to approach this/correct this...it would be great.
Looks as if [ROLLUP_PURP_CD] is a string, so you need to use an aggregate function which makes sense for a string - best bet is probably ATTR() intead of SUM().
That should do it.