Set Analysis with reference column

[alexpanjhc]

Hi

I am trying to use a reference column in my set analysis and I am not sure how it works.

Sum({<STATUS={'OPEN'}, [Tax Unrealized]={"<0"}>}POSITION)

where "Tax Unrealized" is a measure calculated by other fields let's say A-B-C.

I want to sum up the position by all the lines where A-B-C<0 and it is showing me everything. It seems it ignored the [Tax Unrealized]={"<0"}

Where am I wrong?

Thanks!

[albertovarela]

Can you share some sample data?

[sunny_talwar]

Are you saying that Tax Unrealized is actually an expression made up like A-B-C?

[alexpanjhc]

Yes that is correct. A, B, C are three fields. it is actually A-(B-C)

A=Sum({$<[STATUS]={'OPEN'}>}[END MKT VALUE])

B=Sum({$<[STATUS]={'OPEN'}>}[Effective Net Cost])

C=Sum({$<[STATUS]={'OPEN'}>}[Wash Sales])

I realized that may not be accepted by the set analysis, so I created a dimension in the table called  [Tax Loss]

=if (aggr((Sum({$<[STATUS]={'OPEN'}>}[END MKT VALUE]))-(Sum({$<[STATUS]={'OPEN'}>}[Effective Net Cost])-Sum({$<[STATUS]={'OPEN'}>}[Wash Sales])) <0, [Instrument Description], [L/S], STATUS ),'Y','N')

However, this field is not recognized in the set analysis below.  😞

if(STATUS='OPEN' and [Tax Loss]='Y', Sum(POSITION),0)

[Tax Loss] is not recognized...

[alexpanjhc]

Instrument Desc, Exposure, Quantity,Adjusted Date,Status,Tax Unrealized,

A,    L, 50, 1/1/, Open,-300

A, L, 10,2/1,Open,100

A, L, 20, 3/1,Open,-200

A, L, 20, 1/1,Closed,-100

so in this case I need to determine if tax unrealized if less than 0 and if it is open, i will sum up the quantity

that would be 50+20=70

Hope this is clear.

thanks!

[albertovarela]

Based on the sample data this works:

Sum({$<Status={'Open'}, [Tax Unrealized] = {"<0"}>}Quantity)

[alexpanjhc]

Sorry that I misled you.

The Tax Unrealized is not an actual field, it is a measure and the data came with 3 parts and I did my calculations on the fly

[END MKT VALUE]),[Effective Net Cost],[Wash Sales]

100,70,20

20,50,10

the formula was [Tax Unrealized]= Sum({$<[STATUS]={'OPEN'}>}[END MKT VALUE]))-(Sum({$<[STATUS]={'OPEN'}>}[Effective Net Cost])-Sum({$<[STATUS]={'OPEN'}>}[Wash Sales])