Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

Re: How to use Rank and Aggregate while ignoring a selection?

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)

2 Replies

Re: How to use Rank and Aggregate while ignoring a selection?

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)

Not applicable

Re: How to use Rank and Aggregate while ignoring a selection?

Thank you Ruben, that worked perfectly for me!