Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: Set Analysis Two Layers Deep

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

brentski

Contributor III

2020-01-23
03:22 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

593 Views

1 Solution

Accepted Solutions

Nicole-Smith

MVP

2020-01-24
01:38 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

496 Views

8 Replies

Nicole-Smith

MVP

2020-01-23
04:31 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

574 Views

andoryuu

Creator III

2020-01-23
04:35 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

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

571 Views

brentski

Contributor III

2020-01-23
04:43 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

570 Views

brentski

Contributor III

2020-01-23
05:13 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

563 Views

Nicole-Smith

MVP

2020-01-23
05:24 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

560 Views

brentski

Contributor III

2020-01-24
01:23 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

500 Views

Nicole-Smith

MVP

2020-01-24
01:38 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

497 Views

brentski

Contributor III

2020-01-24
01:56 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

490 Views