Set Analysis Two Layers Deep

[brentski]

I truly appreciate this forum.  I do try to search the internet and try a bunch of work prior to posting on the boards.  So, if I can improve on how I write up my questions to get better answers, please provide feedback too.

Question:  I want to get the top Episodes for people who have more than $250,000 in Amount Paid.

I can get all of the people I just can't get the Episodes associated with those people:

Layer 1 Set:  =sum({<[Member ID]={'=sum( [Amount Paid])>=250000'}>} [Amount Paid])

How do I get the second layer?  How do I get the people (member ID) to now represent the top 5 Episodes along with the costs for only the people who have $250k or more in spend?

As always, any guidance or direction is greatly appreciated.

[Nicole-Smith]

Can you post example data and the expected output from that example data?

[andoryuu]

How about this - set up a calculated dimension for your Member ID thusly:
=IF(Aggr(Rank(SUM([Amount Paid]),4),[Member ID])<=5,[Member ID])
Then uncheck "Include Null Values" on the dimension in your table
Your expression can then be set to SUM([Amount Paid]) to see their total amount and only display the top 5 rows.

[brentski]

Yes!  Thank you!

Person   Amount$   Episode

1                $100,000     A--> this could be 10 or more different rows of data (or just one).

1                $150,000     B

2                $150,000     C

2                 $150,000    A

3                $175,000     D

3                 $200,000    A

4                 $50,000       A---> Added this to show dollars should not be included in A (person did not have $250k

Episode A  $450,000

Episode B $175,000

Episode D  $175,000

Episode C  $150,000

[brentski]

Thanks Andoryuu for the response.  I posted an example of the data and the outcome.  I should also state that I am using Qlik Sense through a web interface.  While I have the ability to do Sets, I do not have the ability to customize the data load process.  For the solution you proposed for creating a calculated dimension, I do not have the option to do that.  I can create a Filter Pane using the same idea then just go in and "Select All". After selecting all, I could then just create a table and then create a set using Episode and rank to get the top episodes.  I would have a table of Episode, Sum Amount. 

Interesting approach.

[Nicole-Smith]

I'm confused why you say that your original expression isn't working.

If I load your example data in like this:

LOAD * INLINE [
  Person, Amount, Episode
  1, 100000, A
  1, 150000, B
  2, 150000, C
  2, 150000, A
  3, 175000, D
  3, 200000, A
  4, 50000, A
];

And then make a table with:
Dimension: Episode
Measure: sum({<[Person]={'=sum([Amount])>=250000'}>} [Amount])

It returns a table that looks like this:

A	450000
D	175000
B	150000
C	150000

What isn't working as expected here?

[brentski]

Nicole,

Thanks for continuing to help.  

How do I, in the set, create just a view of the top 2?

My actual list is returning over 1,000 people and 1,500 Episode types.  So I really just want the top 5 episodes based on the 1,000 people's experience.  So my initial set gives me the 1,500 episodes for the costs of the people (based on changing the dimension to episode, like you suggested), but I can't limit the returned episodes to just the top 5 using the set.

Additional thoughts?

[Nicole-Smith · Accepted solution]

Okay, I understand now.

If you use this as your measure, I believe it should work as expected:
if(rank(sum({<[Person]={'=sum([Amount])>=250000'}>} [Amount])) <= 2,
sum({<[Person]={'=sum([Amount])>=250000'}>} [Amount]))

You can alter the 2 to show however many of the top values that you want.

Make sure to go into Add-ons > Data handling and uncheck "Include zero values" in order to hide all of the nulls.

[brentski]

I tried your recommendation in my data and .......IT WORKED!  Thank you so much Nicole!

I was typing away that it had not worked, but I had a typo in one of my field names.