Script Error ")" expected

[#1]

I am getting a script error: ")" expected for this IF statement. Sure would be grateful is somebody can troubleshoot this for me. Thank you.

If TempInvoice_Sys_MR66 <>'SF' and TempHeadQuarters.NUMBER<> '999' and Invoice_EDA_Year=$(=Only(Invoice_EDA_Year)-1), TempDtl_Purchase_Amount) as ePurchasesPY

[Miguel_Angel_Baeyens]

Hi,

I don't know if that's a typo, but you missed the opening parenthesis:

If(TempInvoice_Sys_MR66 <> 'SF' and TempHeadQuarters.NUMBER <> '999' and Invoice_EDA_Year = $(=Only(Invoice_EDA_Year)-1), 
TempDtl_Purchase_Amount) as ePurchasesPY

Note that the $(=) part will cause a "internal error" although it usually keeps running the script, meaning that that part of the If() will never be tested, so I'd use instead:

If(
     TempInvoice_Sys_MR66 <> 'SF' 
     AND TempHeadQuarters.NUMBER <> '999' 
     AND Invoice_EDA_Year = Invoice_EDA_Year -1, // will never match
     TempDtl_Purchase_Amount
) AS ePurchasesPY

Note as well that this third condition will never be matched, in the same record, the field Invoice_EDA_Year can only have one value, say 2012, and not two values, so Invoice_EDA_Year (2012) will never be equal to Invoice_EDA_Year -1 (2011) in the same record, because a value cannot be equal to another value. It seems that you want to do some kind of aggregation or looking for some previous values, and you got to the Only() function.

Hope that helps.

Miguel

[#3]

Ok, thank you for your help, I will work on this.