9 Replies Latest reply: Sep 1, 2017 11:15 AM by Sunny Talwar

# 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 ID Type Class Sales 1 1 A 10 2 1 A 10 3 1 A 10 4 2 C 20 5 2 C 30 6 2 C 10 7 2 C 21 8 3 D 34 9 3 D 21 10 4 D 10 Resulting Table ID Type Class Sales 2 1 A 10 3 1 A 10 5 2 C 30 6 2 C 10

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]),)

I have also attached a small test Qlik Sense QVF.

Thank you!!!

• ###### Re: Complex set analysis question please....

Try this

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

• ###### Re: Complex set analysis question please....

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!

• ###### Re: Complex set analysis question please....

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

• ###### Re: Complex set analysis question please....

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

• ###### Re: Complex set analysis question please....

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]))

• ###### Re: Complex set analysis question please....

Than you very much!!!

Exaclt what I needed, much appreciated

• ###### Re: Complex set analysis question please....

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

• ###### Re: Complex set analysis question please....

Sunny,

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

 ID Type Class Sales Selected ID 2 1 A 10 1 3 1 A 10 1 5 2 C 30 4 6 2 C 10 4

Thanks

• ###### Re: Complex set analysis question please....

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