Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
entsh
Contributor II
Contributor II
‎2021-03-24 07:19 AM

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

 

1,381 Views
0 Likes
1 Solution

Accepted Solutions
Saravanan_Desingh
MVP
MVP
‎2021-03-28 11:07 PM

One solution through Script

tab1:
LOAD * INLINE [
    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
    test, 31, 1, D    
    test, 32, 1, D    
    test, 33, 1, D    
    test, 34, 1, D    
    test, 35, 1, D    
];

Left Join(tab1)
LOAD name, FirstSortedValue(level,-id) As MaxLevel
Resident tab1
Group By name;

Left Join(tab1)
LOAD name, id, AutoNumber(RowNo(),name) as SortID
Resident tab1
Order By name, id Desc;

tab2:
NoConcatenate
LOAD name, If(Sum(number)>=5,'ok','nok') As Result
Resident tab1
Where SortID <= 5 And level=MaxLevel
Group By name;

Drop Table tab1;

View solution in original post

930 Views
0 Likes
11 Replies
marcus_sommer
MVP
MVP
‎2021-03-25 09:53 AM

You may try something like this:

if(if(firstsortedvalue(level, -id, 1) = level and id >= firstsortedvalue(id, -id, 5), sum(number)) >= 5, 'ok', 'nok')

- Marcus

1,031 Views
0 Likes
entsh
Contributor II
Contributor II
‎2021-03-25 11:26 AM
Author

In response to marcus_sommer

thx but It doesn't work

when i set all levels of SO to C THE RESULT must be OK but it shows nOK!

1,024 Views
0 Likes
marcus_sommer
MVP
MVP
‎2021-03-25 12:58 PM

In response to entsh

Just with your shown data the sum should be 3 and therefore not ok. Often it's useful not to apply only the complex expression else also each single part of it to see if they work and return the expected results - which also includes that you definitely knows your loaded data - means putting all here related fields (dimensions/expressions/selections-fields) together with an unique key (if none exists you may add one with rowno() in the script) into a table-box.

Beside this it may helpful to make already certain calculations/matchings within the script to simplify the UI approaches.

- Marcus

1,016 Views
0 Likes
entsh
Contributor II
Contributor II
‎2021-03-25 04:45 PM
Author

for part 1 sum only the number with  level as same as the last level

i used this formula and its worked !

but i think its too long !

is there any better short formula?

 

=if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 3)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 4)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 5)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3),FirstSortedValue(number,-id,4),FirstSortedValue(number,-id,5)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 3)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 4)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3),FirstSortedValue(number,-id,4)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 3)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) , FirstSortedValue(number,-id,1),10)

971 Views
0 Likes
MayilVahanan
MVP
MVP
‎2021-03-26 03:26 AM

In response to entsh

Hi @entsh 

As informed by Marcus,

if you set all levels of SO to C, its 3. Not >=5, so its should shows nOK. 

Can you please explain, how result must be OK.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
994 Views
0 Likes
entsh
Contributor II
Contributor II
‎2021-03-26 04:06 AM
Author

In response to MayilVahanan

Please see the below table

name id number level
SO 8 1 C
SO 9 1 C
SO 12 1 C
SO 20 1 C
SO 26 1 C

when I  set all levels of SO to C, its 1+1+1+1+1=5 so it should show OK (I WANT TO SUM NUMBERS IN COLUMN NUMBER NOT COUNT NAMES !)

BUT 

name id number level
SO 8 0 C
SO 9 0 C
SO 12 1 C
SO 20 1 C
SO 26 1 C

IT IS =1+1+1+0+0 =3

AND THIS

name id number level
SO 8 1 A
SO 9 1 B
SO 12 1 C
SO 20 0 C
SO 26 1 C

IT IS =1+0+1 =2 (SUM NUMBER WITH LEVEL C)

 

I WROTE A FORMULA AND ITS WORKES BUT ITS TOO LONG!

IS THERE ANY BEETER SHORT FORMULA?

=if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 3)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 4)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 5)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3),FirstSortedValue(number,-id,4),FirstSortedValue(number,-id,5)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 3)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 4)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3),FirstSortedValue(number,-id,4)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 3)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) and
firstsortedvalue(level, -id, 2)=firstsortedvalue(level, -id, 1) ,rangesum( FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2)),
if(firstsortedvalue(level, -id, 1)=firstsortedvalue(level, -id, 1) , FirstSortedValue(number,-id,1),10)
)
)
)
)

 

 

 

988 Views
0 Likes
MayilVahanan
MVP
MVP
‎2021-03-26 04:29 AM

In response to entsh

HI @entsh 

Might be, try like below

Slight change based on Marcus solution,

if(if(firstsortedvalue(level, -id, 1) = level and id >= firstsortedvalue(total id, -id, 5), sum(total<level> number)) >= 5, 'ok', 'nok')

MayilVahanan_0-1616747351633.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
980 Views
entsh
Contributor II
Contributor II
‎2021-03-28 10:45 AM
Author

In response to MayilVahanan

thx a lot but

In order to avoid confusion, please find the attached file

the result is ok

IS THERE ANY BEETER SHORT FORMULA?

when I test your formula it doesn't work

nT.qvw
953 Views
0 Likes
Saravanan_Desingh
MVP
MVP
‎2021-03-28 11:07 PM

One solution through Script

tab1:
LOAD * INLINE [
    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
    test, 31, 1, D    
    test, 32, 1, D    
    test, 33, 1, D    
    test, 34, 1, D    
    test, 35, 1, D    
];

Left Join(tab1)
LOAD name, FirstSortedValue(level,-id) As MaxLevel
Resident tab1
Group By name;

Left Join(tab1)
LOAD name, id, AutoNumber(RowNo(),name) as SortID
Resident tab1
Order By name, id Desc;

tab2:
NoConcatenate
LOAD name, If(Sum(number)>=5,'ok','nok') As Result
Resident tab1
Where SortID <= 5 And level=MaxLevel
Group By name;

Drop Table tab1;
931 Views
0 Likes