topic Re: Pivot table not ranking top 5 correctly in QlikView

Pivot table not ranking top 5 correctly

infock12 — Thu, 17 Sep 2015 13:55:43 GMT

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!

Re: Pivot table not ranking top 5 correctly

MK_QSL — Thu, 17 Sep 2015 13:57:18 GMT

Provide sample data or qvw file !

Re: Pivot table not ranking top 5 correctly

Thu, 17 Sep 2015 14:01:51 GMT

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

Then hide the column in a straight table (Chart)

Re: Pivot table not ranking top 5 correctly

marcus_sommer — Thu, 17 Sep 2015 14:11:02 GMT

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

Re: Pivot table not ranking top 5 correctly

infock12 — Thu, 17 Sep 2015 16:54:08 GMT

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.

Re: Pivot table not ranking top 5 correctly

infock12 — Thu, 17 Sep 2015 16:54:23 GMT

Hi Manish,

Will do it shortly.

Thanks,

Karthik