Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Complex set analysis question please....

Hi there,

I would like to select several ID's and return 2 ID's for each ID selected, where Type and Class value match the selected ID.

The below example shows the selection of ID's 1 and 4 to return:

ID's 2 and 3 (first two records with same possible values for Type and Class as ID 1

ID's 5 and 6 (first two records same possible values for Type and Class as ID 4

  

  

Data
IDTypeClassSales
11A10
21A10
31A10
42C20
52C30
62C10
72C21
83D34
93D21
104D10
Resulting Table
IDTypeClassSales
21A10
31A10
52C30
62C10

I have tried the following expression, but am only getting the two selected results (ID 1 and 4), image attached...

IF(RANK(AGGR(Sum({1<ID = ,Type = (P(Type)),Class = (P([Class]))>}[Sales]),ID))<=2,

Sum({1<ID = ,Type = (P(Type)),Class = (P([Class]))>}[Sales]),)

Please can someone advise as to the correct expression?

I have also attached a small test Qlik Sense QVF.

Thank you!!!

9 Replies
sunny_talwar

Try this

Sum({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}[Sales])

Anonymous
Not applicable
Author

Thank you!

This returns all values with the same possible values as the ID's selected, but not only 2 records for each ID selection, therefore ID 7 is still included, image attached.

Any help appreciated!

Expression Test 2.PNG

sunny_talwar

Sorry, I am confused... why should 7 be not selected? In your image, it has the same Type and Class as 4? So we would expect it to show up, right?

In my version, I see this

Capture.PNG

Anonymous
Not applicable
Author

It does have the same type and class as 4, but we want to rank the results to top 2 based on each selection,

for example, if there was 10 ID's with the same Type and Class as ID 4, we only want to show the Top 2 results based on values derived from ID 4.

Only the two results for each selection are required, therefore number of records within the resulting table are GetSelectedCount(ID)*2 (where there are two results for each selection)

Thanks

sunny_talwar

For just seeing two of each, try this

If(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}Aggr(Rank(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}-ID)), Type, Class, ID)) < 3,

Sum({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}[Sales]))

Anonymous
Not applicable
Author

Than you very much!!!

Exaclt what I needed, much appreciated

Anonymous
Not applicable
Author

I am unable to make this answer as correct currently as the actions drop-down isn't generating, but I will try to do this tomorrow.

Thanks again

cmorri1988
Creator
Creator

Sunny,

do you know if there is a way to get the selected ID relevant to each result within the table such as?

  

ID

TypeClassSalesSelected ID
21A101
31A101
52C304
62C10  4

Thanks

sunny_talwar

May be this

If(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}Aggr(Rank(Only({1<ID = p({<Type = p(Type), Class = p(Class), ID = e(ID)>})>}-ID)), Type, Class, ID)) < 3,

Only(TOTAL <Type, Class> ID))

Capture.PNG