Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
brentski
Contributor III
Contributor III

Set Analysis Two Layers Deep

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.

Labels (1)
1 Solution

Accepted Solutions
Nicole-Smith

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.

screenshot.png

View solution in original post

8 Replies
Nicole-Smith

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

andoryuu
Creator III
Creator III

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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:

A450000
D175000
B150000
C150000

 

What isn't working as expected here?

brentski
Contributor III
Contributor III
Author

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

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.

screenshot.png

brentski
Contributor III
Contributor III
Author

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.