Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

deniscamh
Contributor

Rank Function with Multiple dimensions

Hi All,

I have Question about rank function.

I need create pivot table with 4 dimensions: National, Dealer Zone, Dealer District and Dealer. and one measure let say it is sum(sales), (in reality I have much complected measure)

Then I need to rank each Dealer's sale with every dimension.

For example if Dealer's 1 sales will be rank 50 in National Level; rank 20 in Zone level and rank 5 in District level.

Something like below.

Any suggestions?

Ranking.PNG

1 Solution

Accepted Solutions
deniscamh
Contributor

Re: Rank Function with Multiple dimensions

So I just used the aggr function for that and it worked:

for Rank National - aggr(rank($(vCSEAllNational)), [Dealer Code])

for Rank Zone - aggr(rank($(vCSEIndexZone)),[Dealer Zone Code],[Dealer Code])

for Rank District - aggr(rank($(vCSEDistrict)),[Dealer Zone Code],[Dealer District Code], [Dealer Code]).

And this is working fine.

The Total function inside Rank function does not work for some reason.

Thank you Sunny and Digvijay for your help.

19 Replies
Digvijay_Singh
Honored Contributor III

Re: Rank Function with Multiple dimensions

You may need to use Total with rank to get rank at dimension level.

Rank(Total<National>Sum(Sales)) should be considering National Dim for the rank, you can use Total<National,[Dealer Zone]> to rank at dealer level for each national value.

deniscamh
Contributor

Re: Rank Function with Multiple dimensions

Yes I tried that before but it does not work.

I think there is something to do with my expression.

Here is my expression:

(if(sum({<$(vRollingMonthsGeneral)>}(msrConvenienceIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrConvenienceIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrConvenienceIndexQuestionPoints1)),0)

+

if(sum({<$(vRollingMonthsGeneral)>}(msrTreatmentIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrTreatmentIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrTreatmentIndexQuestionPoints1)),0)

+

if(sum({<$(vRollingMonthsGeneral)>}(msrValueIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrValueIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrValueIndexQuestionPoints1)),0)

+

if(sum({<$(vRollingMonthsGeneral)>}(msrQualityIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrQualityIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrQualityIndexQuestionPoints1)),0))*100

-----------------------------------------------------------

vRollingMonthsGeneral is an expression to calculate rolling months:

[Survey Year]=,[Survey Month]=,[Survey Date]= ,[Survey Month Year]=,dimSurveyReceivedDateItself={">$(=monthend(AddMonths(Max(Date),-$(vNumRollinMonth))))<=$(=Max(Date))"}

Then

I create a variable for this expression called vCSE and add the expression the table like this Rank(Total <Dealer Zone> $(vCSE)), but the result I get is NA.

I guess something I need to change in expression?

deniscamh
Contributor

Re: Rank Function with Multiple dimensions

Yes I tried that before but it does not work.

I think there is something to do with my expression.

Here is my expression:

(if(sum({<$(vRollingMonthsGeneral)>}(msrConvenienceIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrConvenienceIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrConvenienceIndexQuestionPoints1)),0)

+

if(sum({<$(vRollingMonthsGeneral)>}(msrTreatmentIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrTreatmentIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrTreatmentIndexQuestionPoints1)),0)

+

if(sum({<$(vRollingMonthsGeneral)>}(msrValueIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrValueIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrValueIndexQuestionPoints1)),0)

+

if(sum({<$(vRollingMonthsGeneral)>}(msrQualityIndexQuestionPoints1))>0,

sum({<$(vRollingMonthsGeneral)>}(msrQualityIndexIndexScore1))/

sum({<$(vRollingMonthsGeneral)>}(msrQualityIndexQuestionPoints1)),0))*100

-----------------------------------------------------------

vRollingMonthsGeneral is an expression to calculate rolling months:

[Survey Year]=,[Survey Month]=,[Survey Date]= ,[Survey Month Year]=,dimSurveyReceivedDateItself={">$(=monthend(AddMonths(Max(Date),-$(vNumRollinMonth))))<=$(=Max(Date))"}

Then

I create a variable for this expression called vCSE and add the expression the table like this Rank(Total <Dealer Zone> $(vCSE)), but the result I get is NA.

I guess something I need to change in expression?

Digvijay_Singh
Honored Contributor III

Re: Rank Function with Multiple dimensions

Did you verify your expression? Hope it works before using it with Rank. Just trying to understand if all code pieces are working individually.

deniscamh
Contributor

Re: Rank Function with Multiple dimensions

Yes it works, and it also works when I do Rank(total $(vCSE)), but when I add <Dealer Zone> it does not work

Digvijay_Singh
Honored Contributor III

Re: Rank Function with Multiple dimensions

Hope you have used [] as the field has space in between.

deniscamh
Contributor

Re: Rank Function with Multiple dimensions

Yes I did

Still does not work

Digvijay_Singh
Honored Contributor III

Re: Rank Function with Multiple dimensions

Can you share app to look further, or share the exact full expression which is not working, I see the field name you are referring in responses is still different than the one in image

Re: Rank Function with Multiple dimensions

Would it be possible for you to share a sample?

Community Browser