topic Re: Pivot table top 10 results in QlikView

Pivot table top 10 results

Fri, 25 Jul 2014 09:33:39 GMT

Hello everyone,

So I am trying to show only the top 10 results on a pivot table.

The file I have in attachment has an example of my entire data and I want to show what is only painted in green. This corresponds to the top 10 descriptions.

I have already used the expression: aggr(if(rank(count(Column 1))Column 3),Column 3) wich is often used to achieve this kind of results, but I cannot have the top 10...

Am I doing something wrong?

Thank you in advance.

Best Regards!

Re: Pivot table top 10 results

MK_QSL — Fri, 25 Jul 2014 09:34:56 GMT

Can you also provide sample data please?

Re: Pivot table top 10 results

aveeeeeee7en — Fri, 25 Jul 2014 09:39:30 GMT

Try something like this:

Sum({$<Category= {"=Rank(Aggr(Sum(Value),Category))<= 10"}>} Value)

Regards

Aviral Nag

Re: Pivot table top 10 results

Fri, 25 Jul 2014 09:42:21 GMT

Hi Ivo,

using your column numbers from that excel, think the below will work for you

=Sum({<[Column 3]={"=rank(sum([Column 4]))<= 10"}>} [Column 4])

hope that helps

Joe

Re: Pivot table top 10 results

Fri, 25 Jul 2014 10:19:12 GMT

So I am exploring the expressions you guys suggested.

Meanwhile here is the sample.

Note that if you change something and send the file back to me, I only have the personal edition, so I won't be able to open a file saved with the professional edition.

Thank you for the support.

Re: Pivot table top 10 results

Fri, 25 Jul 2014 10:25:17 GMT

From that example then use

Sum({<Column_2={"=rank(sum(Column_3))<= 10"}>} Column_3)

in your expression and move column 4 to a dimension

Joe

Re: Pivot table top 10 results

Fri, 25 Jul 2014 10:27:33 GMT

With this expression I get a // Error in calculated dimension

Re: Pivot table top 10 results

Fri, 25 Jul 2014 10:30:01 GMT

Also the expression // Error in calculated dimension, on column 2.

I can't move column 4 to dimension because I need an expression for the table to show results

Re: Re: Pivot table top 10 results

aveeeeeee7en — Fri, 25 Jul 2014 10:35:29 GMT

Use this Expression:

Sum({$<Column_2 = {"=Rank(Aggr(Sum(Column_3),Column_2))<= 10"}>} Column_3)

Also, see the Attachement.

Hope, it helps you.

Regards

Aviral Nag

Re: Pivot table top 10 results

Fri, 25 Jul 2014 10:37:30 GMT

Hi Ivo,

I just used your example and got no such error.

You will still have an expression so will get results, all I have done is move the expression for column 4 to a dimension and replaced with my new expression.

Please see below

Re: Pivot table top 10 results

Fri, 25 Jul 2014 10:38:08 GMT

Still the same result.

And I can not open the file because I only have personal edition.

Thank you!

Re: Pivot table top 10 results

Fri, 25 Jul 2014 12:04:21 GMT

Hello Joseph,

I did not understood you properly and was defining a dimension with the expression you gave me instead of a expression.

Now I got to the same result as you show on the image, but there is still a problem for me.

The Description A has 25 different results (from column 3) that I need to show, and with this expression it only shows 5.

This is also shown on your image.

I don't understand why.

Thank you for the help!

Re: Pivot table top 10 results

Fri, 25 Jul 2014 12:49:30 GMT

I see, the sum is an aggregation function so it is in effect rolling up all the entries under column_3 and displaying the aggregated answer across your dimensions.

If you need this to show row by row like that, you can add a new more granular field to your script, for instance

rowno() as Row_No

and then add that as a dimension, it will then give you the result across this new dimension i.e your 25 rows

hope that helps

Joe

Re: Pivot table top 10 results

Fri, 25 Jul 2014 13:33:04 GMT

Hi Joseph,

I don't understand how the rowno function works. I will try to explore this but I don't know if it will work.

Thank you for your help!

Re: Pivot table top 10 results

Fri, 25 Jul 2014 13:40:11 GMT

Have a look in the QV manual is a good start, it will help explain what the function does.

Basically it will number each row of your data as it is loaded.

From your example change the script to the below and then add that new field as a dimension

LOAD Column_1,

Column_2,

Column_3,

Column_4,

RowNo() as Row_No

FROM

(ooxml, embedded labels, table is Sheet10);

Re: Re: Pivot table top 10 results

Fri, 25 Jul 2014 14:12:45 GMT

Well this is actually a nice trick and it works, but now the value of rowno are showing. Is there a way of hiding this column?