19 Replies Latest reply: Feb 13, 2018 9:41 AM by Denis LOMAKIN

# 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?

• ###### 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.

• ###### 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?

• ###### 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.

• ###### 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

• ###### Re: Rank Function with Multiple dimensions

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

• ###### Re: Rank Function with Multiple dimensions

Yes I did

Still does not work

• ###### 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

The pivot is showing null in dimension, may be due to extension in the left top having some special selections required, can you share how to populate data even without using the expression?

• ###### Re: Rank Function with Multiple dimensions

The extension is used to  choose number of Months for this expression

vRollingMonthsGeneral:

You can find this variable in the variable section and change vNumRollinMonth to 3.

Hope it make sense.

• ###### Re: Rank Function with Multiple dimensions

Do we need to make any other selections? It still shows '-' for the dimension

• ###### Re: Rank Function with Multiple dimensions

The reason the data is not showing is because I loaded only first 500 rows and there probably no data for that calculation

• ###### Re: Rank Function with Multiple dimensions

I only see the rows when add '=' in front of vCSEDealercode variable, but \$(vCSEDealerCode) is showing 0 always.

• ###### Re: Rank Function with Multiple dimensions

It is probably because there is no data for a period I loaded.

I loaded only 500 rows in order to be able to share the app.

It is a huge app.

I was hoping by looking on the structure you can help me to figure out what's wrong.

• ###### Re: Rank Function with Multiple dimensions

I came up with somting like that aggr(rank(\$(vCSEDealerCode)),[Dealer Zone Code],[Dealer Code]) for zone

and aggr(rank(\$(vCSEDealerCode)),[Dealer Zone Code],[Dealer District Code], [Dealer Code]) for District.

but the problem is how I can ignore the filters now?

• ###### 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?

• ###### Re: Rank Function with Multiple dimensions

Would it be possible for you to share a sample?

• ###### 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.