Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
GinaL
Contributor
Contributor

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

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)

Labels (2)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

This should work:

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

View solution in original post

2 Replies
Or
MVP
MVP

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
MVP
MVP

This should work:

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