Pivot table not ranking top 5 correctly

[infock12]

Hello all,

Good morning. I have a pivot table with two dimensions, Student and Category

The expression I have is =Num(count({<Rating={'>=7'}>} Questions)/Count(Questions), '0.00%')

I wanted to rank them by top 5 by ‘Category’ and tried the following and it works.

=if(Aggr(Rank(Num(count({<Rating={'<7'}>} Questions)/Count(Questions)), '0.00%'), Category)<=5, Category)

However, it does not rank properly. For example, Category A has 2 similar values (63%) that are coming out at top 5, but this formula is listing only one 63% category nd moves on to the next highest category (58%) instead of listing that 63% twice. Could you please let me know why it is?

Is it something I will have to change in the expression? Coz, the expression does not have ‘Category’ in the formula.

Thanks!

[MK_QSL]

Provide sample data or qvw file !

[#3]

you can add the category as dimension and order it as you like.

Then hide the column in a straight table (Chart)

[marcus_sommer]

The easiest way would be to use a straight-table instead of a pivot-chart and the configure it within the tab restrictions.

To sort a pivot is more complex and often you will need dual() to create your calculated dimension, try:

= dual(if(Aggr(Rank(Num(count({<Rating={'<7'}>} Questions)/Count(Questions)), '0.00%'), Category)<=5, Category),

     if(Aggr(Rank(Num(count({<Rating={'<7'}>} Questions)/Count(Questions)), '0.00%'), Category)<=5,           Aggr(Rank(Num(count({<Rating={'<7'}>} Questions)/Count(Questions)), '0.00%'), Category)))

- Marcus

[infock12]

Hi Marcus,

Sorry, it didn't work...I will try it again tomorrow and let you know.

Hi Gennaro,

That didn't work either unfortunately. I will upload the file shortly.

Thanks again.

[infock12]

Hi Manish,

Will do it shortly.

Thanks,

Karthik