topic Re: How to show only Top 10 values in pivot table in QlikView

How to show only Top 10 values in pivot table

Tue, 30 Jun 2015 09:47:55 GMT

Hi Frnz,

Please help me. I would like display Top 10 values. But I am getting all values. Please find the attached QVW.

Re: How to show only Top 10 values in pivot table

PrashantSangle — Tue, 30 Jun 2015 09:55:15 GMT

Hi,

Use Rank()

Search in Community,

Top 10 using Rank()

Regards,

Re: How to show only Top 10 values in pivot table

jsingh71 — Tue, 30 Jun 2015 09:56:00 GMT

See This:

Calculating TOP 10 VALUES in PIVOT TABLE

Re: How to show only Top 10 values in pivot table

Gysbert_Wassenaar — Tue, 30 Jun 2015 09:58:47 GMT

The top 10 What of What?

Re: How to show only Top 10 values in pivot table

buzzy996 — Tue, 30 Jun 2015 10:00:00 GMT

goto --dimeniosn limits tab--

see below,

Re: How to show only Top 10 values in pivot table

Tue, 30 Jun 2015 10:02:03 GMT

YEAR wise Top 10 and I need to show Account name also in Pivot table

Re: How to show only Top 10 values in pivot table

Tue, 30 Jun 2015 10:02:53 GMT

In Pivot table, no dimension limits tab

Re: How to show only Top 10 values in pivot table

buzzy996 — Tue, 30 Jun 2015 10:05:48 GMT

then u can use the rank function on ur expression.

some thing like this..

=Sum(Aggr(if(Rank(Sale)<=10,on-whichever the field ur calulcating fro example sales),Account name,Sale))

Re: How to show only Top 10 values in pivot table

MK_QSL — Tue, 30 Jun 2015 10:07:57 GMT

Use this as calculated Dimension instead of L4ACCTNAME

=AGGR(IF(Rank(SUM(ULT_REVENUE),4)<=10,L4ACCTNAME),YEAR, L4ACCTNAME)

Tick suppress when value is null

Re: How to show only Top 10 values in pivot table

Tue, 30 Jun 2015 10:09:40 GMT

I will try..Thanks

Re: How to show only Top 10 values in pivot table

Tue, 30 Jun 2015 10:15:37 GMT

Hi Manish.,

I am not getting..Can you please tell me which one i have to take in expression tab

Re: How to show only Top 10 values in pivot table

sunny_talwar — Tue, 30 Jun 2015 10:15:38 GMT

Trying to get this?

Re: How to show only Top 10 values in pivot table

krishna20 — Tue, 30 Jun 2015 10:25:03 GMT

Hi ,

Try this

=if(aggr(rank(sum(Cost)),Year)<=10,Account_Name)

Re: How to show only Top 10 values in pivot table

MK_QSL — Tue, 30 Jun 2015 10:32:26 GMT

Use Pivot Table

Dimension

1) YEAR

2) Calculated DImension

=AGGR(IF(Rank(SUM(ULT_REVENUE),4)<=10,L4ACCTNAME),YEAR, L4ACCTNAME)

TICK SUPPRESS WHEN VALUE IS NULL

Expression

Whatever you want

Re: How to show only Top 10 values in pivot table

Tue, 30 Jun 2015 10:38:10 GMT

Hi SunIndia,

Thanks for your Answer. This expression also working.

Re: How to show only Top 10 values in pivot table

sunny_talwar — Tue, 30 Jun 2015 11:34:41 GMT

No problem

I am glad we were able to help.

Best,

Sunny

Re: How to show only Top 10 values in pivot table

Tue, 07 Jul 2015 07:47:17 GMT

Hi SunIndia,

May I know why are you using ** 4,1** in that expression.Can you please let me know.It is working nice. I would like to know please.

If(rank(TOTAL sum(L4AMID_VALUE_USD),4,1) <=15, sum(L4AMID_VALUE_USD))

Re: How to show only Top 10 values in pivot table

MK_QSL — Tue, 07 Jul 2015 08:12:52 GMT

rank([ total ] expression [ , mode [, format ] ])

Evaluates expression, compares the result with the result of the other rows containing the current column segment and returns the ranking of the current row within the segment. For bitmap charts, the current column segment is defined as it appears in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure).

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order.

The ranking is returned as a dual value, which in the case when each row has a unique ranking will be an integer between 1 and the number of rows in the current column segment.

In the case where several rows share the same ranking, the text and number representation can be controlled as follows:

The second parameter mode specifies the number representation of the function result.

mode

0 (default)	If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1	Lowest rank on all rows.
2	Average rank on all rows.
3	Highest rank on all rows.
4	Lowest rank on first row, then incremented by one for each row.

The third parameter format specifies the text representation of the function result.

format

0 (default)	Low value&' - '&high value on all rows (e.g. 3 - 4).
1	Low value on all rows.
2	Low value on first row, blank on the following rows.

The order of rows for mode 4 and format 2 is determined by the sort order of the chart dimensions.

Re: How to show only Top 10 values in pivot table

Tue, 07 Jul 2015 09:37:50 GMT

Hi Manish,

Thanks for your explanation. Now I got idea.