Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Can you share some sample data?
Are you saying that Tax Unrealized is actually an expression made up like A-B-C?
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...
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!
Based on the sample data this works:
Sum({$<Status={'Open'}, [Tax Unrealized] = {"<0"}>}Quantity)
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])