# Qlik Sense App Development

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?

1 Solution

Accepted Solutions
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
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.

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:

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?

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:

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?

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.

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

Honored Contributor III

## Re: Rank Function with Multiple dimensions

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

Contributor

## Re: Rank Function with Multiple dimensions

Yes I did

Still does not work

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

MVP

## Re: Rank Function with Multiple dimensions

Would it be possible for you to share a sample?