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?

[deniscamh]

please see the app aatached

[deniscamh]

Hi Sanny,

Please see the attached.

[Digvijay_Singh]

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?

[deniscamh]

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

vRollingMonthsGeneral:

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

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

Hope it make sense.

[sunny_talwar]

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

[deniscamh]

In Adds on Select Include Zero values please.

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

[Digvijay_Singh]

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

[deniscamh]

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?

[deniscamh]

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.

[deniscamh · Accepted solution]

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.