Rank Function with Multiple dimensions

[deniscamh]

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?

[Digvijay_Singh]

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]

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]

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]

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]

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]

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

[deniscamh]

Yes I did

Still does not work

[Digvijay_Singh]

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

[sunny_talwar]

Would it be possible for you to share a sample?