Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
entsh
Contributor II
Contributor II

sumif last n records number with conditions

hi

Please see the below table

nameidnumberlevel
teh10A
teh20A
teh31A
teh41A
SO81A
SO91B
SO121C
teh151B
teh191B
SO201C
teh251B
SO261C

i want sum last 5 id numbers for each name -if sum>=5 show "ok" if not show "nok" 

=if(RangeSum(FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3),FirstSortedValue(number,-id,4),FirstSortedValue(number,-id,5))>=5,'OK','NOK')

but i want to sum only the number with  level as same as the last level
for example for "teh" the last record level (id=25) is "B" so this condition has to be check for  last 5 id numbers with "B" level only

solution for "teh": (last record id=25 and level=b  and number=1 so result = 1",n-1  record id=19 and level=b  and number=1 so result=1

n-2  record id=15 and level=b  and number=1 so result=1 ,n-3  record id=4 and level=A  and number=1 so result=0

n-4  record id=3 and level=A  and number=1 so result=0 final result =1+1+1+0+0=3 then its nok)

 

RESULT

NAMERESULT
tehnok
sonok

 

11 Replies
Saravanan_Desingh

Output:

commQV56.PNG

entsh
Contributor II
Contributor II
Author

I value the insights and guidance you provide ,your answer is correct

do you know how to solve the question through expressions?

(my formula is so long and i think there is a better and short answer)