Create new business flag using sum in script

[#1]

Hi,

I'm trying to create a flag for each invoice that would indicate if the invoice represents new business. New business being defined as an item being sold to a customer that was not sold to that customer the prior year.

So I need to get the sum of the prior year sales and see if it is 0 to set the new business flag. Currently I have this:

let vNBflag= if( sum(if(InvoiceYear=($(vCyear) - 1) and Customer = $(vCust) and [Item Number] = '$(vItem)', InvSales)) > 0, 1, 0);

where the variables are set using Peek() on the invoice table once it is built. Every time I get a script line error, but I feel there may be a a better way to create this flag. Any tips would be appreciated

-Thanks

[#2]

What about if(sum({$<[Item Number]={"$(=vItem)"},[InvoiceYear]={"$(=vCyear -1)"}>}InvSales=0,1,0)? Using set expressions might get you around your roadblock.

[#3]

I had tried set analysis before. It wouldn't even give me an error message, the script would just exit when it tried to execute the line. It would always evaluate (seemingly) correctly to something like:

let vNBCal = if( sum( {<InvoiceYear={2007}, Customer={0092851}, [Item Number]={"044.00004"}>} InvSales) > 0, 1, 0)

However, in the script editor, the first '{' has a red squiggly line under it and I can't seem to figure out why.