HRank returns NULL in Pivot table

[#1]

I have a dataset (given below). Here I have Zone, State and Type are dimensions of pivot table (in the same order as I mentioned).

I can easily find two metrics: Total Employed, %age of Total employed.

But I struggle to find the third where I need to RANK of  %age of Total Employed. I am using an expression: =HRank((sum(Employed)/Sum(Total_Persons)) *100)

////Attached is the screenshot of what I get.

//// dataset

load * inline [
Zone, State, Type, Employed, Total_Persons
East, A, Rural, 112, 156
East, A, Urban, 87, 123
East, B, Rural, 67, 101
East, B, Urban, 89, 105
East, C, Rural, 145, 189
East, C, Urban, 76, 123
East, D, Rural, 331, 360
East, D, Urban, 259, 290
West, K, Rural, 189, 300
West, K, Urban, 213, 564
West, L, Rural, 435, 789
West, L, Urban, 312, 461
West, M, Rural, 431, 552
West, M, Urban, 202, 401
West, N, Rural, 601, 874
West, N, Urban, 671, 721
North, U, Rural, 202, 256
North, U, Urban, 907, 982
North, V, Rural,167, 301
North, V, Urban,289, 305
North, W, Rural, 645, 789
North, W, Urban,476, 623
South, P, Rural, 389, 400
South, P, Urban, 263, 364
South, R, Rural, 495, 789
South, R, Urban, 612, 761
South, S, Rural, 631, 752
South, S, Urban, 402, 501
];

[swuehl]

It seems you haven't got any pivoted dimensions (pivoted to the top), so you would need to use vrank() or rank() functions.

[#3]

Thanks for your comment...but I am unable to understand where I am doing wrong. I mean I have 'Zone' as pivoted dimension here.

[swuehl]

From your image, it doesn't look as Zone is pivoted to the top, it's still a vertical dimension,

If you tried pivoting to the top and you don't get a result, there might be a different issue, maybe with using multiple dimensions and partial sums.

Could you upload a small sample QVW?

[Digvijay_Singh]

as swuehl said your formula is working when one of the dimension is pivoted horizontal, just tested it.

[#6]

Here I attached a sample QVW. Could you please take a look what exactly should I do to get ranking against Zone (I mean North Zone should have rank 1 West to have 4)?

[swuehl · Accepted solution]

Just drag Zone to the top (and maybe add partial sums), see attached

[swuehl]

Or leave the chart layout as is and use

=Rank((sum(Employed)/Sum(Total_Persons)) *100)

[#9]

Thank you!