hi
Please see the below table
| name | id | number | level |
| teh | 1 | 0 | A |
| teh | 2 | 0 | A |
| teh | 3 | 1 | A |
| teh | 4 | 1 | A |
| SO | 8 | 1 | A |
| SO | 9 | 1 | B |
| SO | 12 | 1 | C |
| teh | 15 | 1 | B |
| teh | 19 | 1 | B |
| SO | 20 | 1 | C |
| teh | 25 | 1 | B |
| SO | 26 | 1 | C |
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
