How to find the Max of field and reflect the corresponding of another field

[GinaL]

Hi,

I am trying to determine the product code in a particular status. My data looks like this

PdtCode	SeqNo	Status
3025	5	-
3025	10	OUT
3025	15	AVAIL
3025	20	OS
3025	25	AVAIL
2554	5	OUT
2554	10	AVAIL
2554	20	OS
3566	5	OUT
3566	10	OUT
3566	15	OS
3871	400	AVAIL
3871	475	OUT
3871	500	OUT
3871	600	OS

I would like to create a KPI chart that shows the number of PdtCode with latest Status = 'OS'

Latest status is determined by the Max Seq by PdtCode. 

PdtCode	Max SeqNo	Latest Status
3025	25	AVAIL
2554	20	OS
3556	15	OS
3871	600	OS

The expected count of OS will be 3. 

I would like to achieve this through charting instead of scripting. 

Have attempted with below but failed

count( distinct {<if(aggr(max({1} total <PdtCode> SeqNo), PdtCode)= SeqNo, Status, '0')={'OS'}>}  PdtCode)

[Or]

Have a look at using FirstSortedValue() instead, perhaps?

FirstSortedValue(SeqNo, -SeqNo) // You could also just use Max(SeqNo) for this, I believe

FirstSortedValue(Status, -SeqNo)

[tresesco · Accepted solution]

This should work:

=Count( DISTINCT if(Aggr( FirstSortedValue(Status, -SeqNo), PdtCode)='OS', PdtCode))