Qlik Community

Ask a Question

New to QlikView

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

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to use Rank and Aggregate while ignoring a selection?

Dear all,

I want to find the company with the highest sales of the product 'Pencil'. I have a calculated dimension that works when all companies are selected. However, for some reason I need the dimension to work when only one company is selected and that does not work.

This is the dimension I use:

=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 2, Companies), Companies)

Does someone know the solution?

Many thanks!

Mark

1 Solution

Accepted Solutions

Hi MArk, if you don't want the selections to filter you can use a dimension like:

=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 1, Only({1}Companies)), Companies)

(With "supress null values" checked)

and a expression like:

SUM({1< Year = {2014}, Product = {Pencil}>} Sales)

View solution in original post

2 Replies

Hi MArk, if you don't want the selections to filter you can use a dimension like:

=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 1, Only({1}Companies)), Companies)

(With "supress null values" checked)

and a expression like:

SUM({1< Year = {2014}, Product = {Pencil}>} Sales)

View solution in original post

Not applicable

Thank you Ruben, that worked perfectly for me!