topic Re: Fixate Rank ( IF in QlikView

Fixate Rank ( IF

vinieme12 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I want to be able to fixate the rank ignoring all selections in ProdName for Brand Field

Currently I have the below table, but the rank changes on selections in the Prodname field even after adding {1} to all aggregations

Current Expression:

=AGGR(rank(sum({1}AGGR(

if(count(distinct Country#)>3 //sale rows in more than 3 countries

and

AGGR(rank(sum({1}Sales),4,1),Brand,ProdName)<=5 // Ranks among Top 5 within the Brand

, sum({1}Sales)),

Brand,ProdName)),4,1),Brand,ProdName)

Expected Output:

MK_QSL — Sun, 08 Apr 2018 11:13:48 GMT

I am not able to get the exact output you have mentioned...

Rank is wrong and Sales is also wrong..

Try my below suggestion and let me know if you think this not work for you..

Create a Pivot Table

Dimension

Brand

ProdName

Expression

Aggr(

IF(Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1)<=5,

SUM({1}Sales))

,Brand,ProdName)

and

Aggr(

IF(Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1)<=5,

Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1))

,Brand,ProdName)

MK_QSL — Sun, 08 Apr 2018 11:18:10 GMT

Or use this expressions

IF(Rank(SUM({1}Sales),4,1)<=5,

Aggr(Rank(SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales),4,1)

,Brand,ProdName))

and

Aggr(

IF(Rank(SUM({1}Sales),4,1)<=5,

SUM({1<ProdName = {"=COUNT({1}DISTINCT Country#)>3"}>}Sales))

,Brand,ProdName)