I can't get this to spell out properly, even with the set analysis wizard...
I have a set of CUSIP in table VAL_RPT (Val_RPT.CUSIP). I have to sum the market value of these securities with VAL_RPT.MKT_VALUE .
I want to restrict the CUSIPs I have in the VAL_RPT to thos that exist in the index table, for the S&P/TSX index.
I'd filter for S&P, get a list of CUSIP, then exclude those from my VAL_RPT.CUSIP list.
I would then be left with only CUSIPS that are not part of the index. I need to add the market values for those securities (CUSIP).
Here's what the wizard had to say:
sum({< VAL_RPT.CUSIP -= E({1<[Name (Benchmark)] ={'S&P/TSX Composite TR Index'}>}VAL_RPT.CUSIP>} VAL_RPT.MKT_VALUE)
But that returns zero, while with individually selected CUSIPs I get 83Million...
Any help is appreciated