Announcements
cancel
Showing results for
Did you mean:
Contributor II

## sumif last n records number

hello

 name id number teh 1 0 teh 2 0 teh 3 1 teh 4 0 teh 15 1 teh 19 1 teh 25 1 SO 8 1 SO 9 1 SO 20 1 SO 26 1 SO 12 1

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

RESULT

 NAME RESULT teh nok so ok

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

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

If I change the input to :

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:

Regards,
Taoufiq ZARRA

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

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

@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:

Regards,
Taoufiq ZARRA

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

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

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

If I change the input to :

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:

Regards,
Taoufiq ZARRA

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

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

@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:

Regards,
Taoufiq ZARRA

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

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