Calc Field or Expression or ...

[robertsb41]

I'm working with 4 fields, ID, Bidder, Rank, Amount.  I want to create a field called Low, ideally in data manager but not critical.

For each ID, I want to display in the low field if Bidder = Rank 1, then display Amount for Bidder =Rank2.  But if Bidder is Rank >=2, then display Amount for Bidder=Rank1.   So my data would look like this:

ID	Bidder	Rank	Amount	Low
10	A	1	10	20
10	B	2	20	10
10	C	3	30	10
10	D	4	40	10
20	D	1	100	200
20	B	2	200	100
20	A	3	300	100
20	C	4	400	100

Thank you!

[Kushal_Chawda · Accepted solution]

add below expression

=if(Rank>=2, sum(total <ID>{<Rank={1}>}Amount),
sum(total <ID>{<Rank={2}>}Amount))