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: 
entsh
Contributor II
Contributor II

sumif last n records number

hello

please look at this table

nameidnumber
teh10
teh20
teh31
teh40
teh151
teh191
teh251
SO      8              1
SO     9              1
SO201
SO    26              1
SO121

 

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

RESULT

NAMERESULT
tehnok
sook

 

note1=id is a number and unique 

note2=the last 5 ids means last larger ids (example: for the "teh" the last large ids are 25,19,15,4,3)

note3=it has to be dynamic ,I want to add a rows

thanks a lot

2 Solutions

Accepted Solutions
Taoufiq_Zarra

@entsh  if I understood correctly both tech and so <5 so its nok

If I change the input to :

Taoufiq_Zarra_0-1616573455391.png

you can for example add dimension with : name

and measure with :

=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')

 

the output:

Taoufiq_Zarra_1-1616573515690.png

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Taoufiq_Zarra

@entsh  in this case :

dimension : name

measure :

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

 

output:

Taoufiq_Zarra_0-1616577821386.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

@entsh  if I understood correctly both tech and so <5 so its nok

If I change the input to :

Taoufiq_Zarra_0-1616573455391.png

you can for example add dimension with : name

and measure with :

=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')

 

the output:

Taoufiq_Zarra_1-1616573515690.png

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Taoufiq_Zarra

@entsh  in this case :

dimension : name

measure :

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

 

output:

Taoufiq_Zarra_0-1616577821386.png

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉