Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Provide sample data or qvw file !
you can add the category as dimension and order it as you like.
Then hide the column in a straight table (Chart)
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
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.
Hi Manish,
Will do it shortly.
Thanks,
Karthik