Set analysis

[jeevays7]

Hi All,

The below is my data. 

Currency	Settlement_Currency	InstrumentType	Market Value	Output
AUD	AUD	Fx Forward	1,535,852.50	0.00
AUD	AUD	Equity Future	443,468.48	0.00
AUD	AUD	Bond Future	280,419.84	0.00
AUD	AUD	Interest Rate Future	78,829.03	0.00
BRL	CHF	Cash	56,774.73	56,774.73
BRL	AUD	Cash	54,574.73	0.00
BRL	BRL	Fx Forward	1,031,776.09	1,031,776.09
CAD	CAD	Fx Forward	1,095,191.66	1,095,191.66
CAD	AUD	Cash	579,704.99	0.00
CAD	CAD	Equity Future	292,857.42	292,857.42
CAD	CAD	Bond Future	-179,203.01	-179,203.01
CAD	CAD	Interest Rate Future	-190,176.83	-190,176.83
CHF	CHF	Fx Forward	1,905,331.94	1,905,331.94
CHF	CHF	Cash	0.00	0.00

I need to write a condition in Output column as IF Currency='AUD' then output is "0" OR

Settlement_currency='AUD' and InstrumentType='Cash' then '0' else MarketValue. how to write the above conditions using set analysis? 

[sunny_talwar · Accepted solution]

May be this

Sum({<Currency = e({<Currency = {'AUD'}>})>-<Settlement_Currency = {'AUD'}, InstrumentType = {'Cash'}>} [Market Value])

[jeevays7]

Thank you Sunny.