I've got a simple Straight Table, where the columns are "Stock" (text strings), "Fund Weight" (numeric data read in rather than calculated), "Index Weight" (numeric data read in rather than calculated), and "Active Weight" (numeric data read in rather than calculated). It all displays fine at the moment. However, I want to exclude one value from the "Stock" column (the "stock" to exclude is "Cash" as this line is not relevant to this table of data).
I'm trying to use Set Analysis to exclude the value, but am struggling because modifiers seem to need a function, and given that the data is text not numeric it's not relevant to put a function (such as "Sum") in. I've tried a few things - here are the results:
-----------------------------------------------------------------------------------------
(1) Writing the modifier as I think it "should" be, and then trying "Sum" as a dummy function:
=sum( {$ < Stock=Stock-{"Cash"} >} Stock)
- as expected, this doesn't work with my non-numeric data - it just returns one row of "0" in the Stock column
(2) Writing the modifier as I think it "should" be, and then trying "Only" as a dummy non-numeric function:
=Only( {$ < Stock=Stock-{"Cash"} >} Stock)
- as expected, this doesn't work as there is more than one value to return so the "only" does not make sense
(3) Writing the modifier as I think it "should" be, and then omitting the function completely (which feels "right" to me):
= {$ < Stock=Stock-{"Cash"} >} Stock
- this just returns " Garbage after expression : "$" " in the Edit Expression dialogue
(4) Using concat as a function that deals with non-numeric data:
=concat( {$ < Stock=Stock-{"Cash"} >} Stock)
- as expected, this only gives one line (all the stocks concatenated) for the Stock column, rather than splitting out the different values (excluding Cash)
(5) Putting "Stock" as a calculated dimension instead of an expression:
=if(Stock<>'Cash', Stock, 0)
- this doesn't get the required result as there is just a 0 in place of the "Cash" under the Stock column, and the 3 Weight expressions in the other columns are still read in
-----------------------------------------------------------------------------------------
I'd really appreciate it if anybody has any ideas how to get around this problem, or which function I could use. Thanks!
