topic Re: Complex set analysis question please.... in App Development

Complex set analysis question please....

Anonymous — Thu, 31 Aug 2017 19:20:40 GMT

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

Please can someone advise as to the correct expression?

I have also attached a small test Qlik Sense QVF.

Thank you!!!

Re: Complex set analysis question please....

sunny_talwar — Thu, 31 Aug 2017 19:26:12 GMT

Try this

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

Re: Complex set analysis question please....

Anonymous — Thu, 31 Aug 2017 19:50:30 GMT

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

sunny_talwar — Thu, 31 Aug 2017 19:53:45 GMT

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

Anonymous — Thu, 31 Aug 2017 19:58:20 GMT

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

sunny_talwar — Thu, 31 Aug 2017 19:58:23 GMT

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

Anonymous — Thu, 31 Aug 2017 20:01:45 GMT

Than you very much!!!

Exaclt what I needed, much appreciated

Re: Complex set analysis question please....

Anonymous — Thu, 31 Aug 2017 20:04:26 GMT

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

cmorri1988 — Fri, 01 Sep 2017 14:48:43 GMT

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

sunny_talwar — Fri, 01 Sep 2017 15:15:18 GMT

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