topic HRank returns NULL in Pivot table in QlikView

HRank returns NULL in Pivot table

Sun, 13 Mar 2016 17:40:46 GMT

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
];

Re: HRank returns NULL in Pivot table

swuehl — Sun, 13 Mar 2016 17:42:15 GMT

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

Re: HRank returns NULL in Pivot table

Sun, 13 Mar 2016 18:32:44 GMT

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

Re: HRank returns NULL in Pivot table

swuehl — Sun, 13 Mar 2016 18:46:26 GMT

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?

Re: HRank returns NULL in Pivot table

Digvijay_Singh — Sun, 13 Mar 2016 18:53:20 GMT

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

Re: HRank returns NULL in Pivot table

Sun, 13 Mar 2016 18:58:44 GMT

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

Re: HRank returns NULL in Pivot table

swuehl — Sun, 13 Mar 2016 19:06:51 GMT

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

Re: HRank returns NULL in Pivot table

swuehl — Sun, 13 Mar 2016 19:08:37 GMT

Or leave the chart layout as is and use

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

Re: HRank returns NULL in Pivot table

Mon, 14 Mar 2016 06:45:16 GMT

Thank you!