topic Re: How to display Top 10 rows in pivot table? in QlikView

How to display Top 10 rows in pivot table?

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a pivot table as mentioned below:

---------------Dimension-------------------------------- ------------------------Expression--------------------------------------

Company Sector Country Revenue (Previous Year) Revenue (Present Year)

ABC Ltd. Pharma China 1000 1500

Singapore 500 700

Sub-Total 1500 2200

XYZ Ltd. Chemicals UK 450 300

France 800 1200

Sub-Total 1250 1500

DEF Ltd. IT USA 2500 3700

Canada 1300 1800

Sub-Total 3800 5500

Sub totals are displayed at the level of country. Company is sorted in descending order based on Revenue (Present Year). I want to only show the Top 10 Companies based on sub totals at country level of Revenue (Present Year).

Can you please help me out.

With regards,

Ayandeep

Re: How to display Top 10 rows in pivot table?

devarasu07 — Tue, 02 May 2017 07:34:46 GMT

Hi,

Try like below,

Method1:

In your dimension:

=if(aggr(RANK(Sum(Revenue),4),Country, Sector,Company)<=10,aggr(RANK(Sum(Revenue),4),Country, Sector,Company))

Method2:

Using Set Expression ,

= SUM({<Country= {"=Rank(SUM(Revenue),4)<=10"}>}Revenue)

Method3:

Using Dimension Limit (only in straight table)

Dimension = Country

Expression = SUM(Revenue)

Dimension Limits

Select Show only Largest 10 Values

Hope This helps you

Thanks,

Deva

Re: How to display Top 10 rows in pivot table?

Tue, 02 May 2017 07:48:38 GMT

Hi Devarasu,

Thanks for your response but it's not sufficing my requirement. Based on the data let's consider I need Top 2 Companies.

So, I want the output to be as below.

---------------Dimension-------------------------------- ------------------------Expression--------------------------------------

Company Sector Country Revenue (Previous Year) Revenue (Present Year)

DEF Ltd. IT USA 2500 3700

Canada 1300 1800

Sub-Total 3800 5500

ABC Ltd. Pharma China 1000 1500

Singapore 500 700

Sub-Total 1500 2200

Total 5300 7700

Please let me know what needs to be done.

With regards,

Ayandeep

Re: How to display Top 10 rows in pivot table?

devarasu07 — Tue, 02 May 2017 08:05:23 GMT

Hi,

It should work, if still not working as expected share your document with mock data. tks

FYI,

Re: How to display Top 10 rows in pivot table?

Tue, 02 May 2017 09:49:54 GMT

Hi Devarasu,

I tried using set analysis expression and its working. However as mentioned in the example in my data I have records corresponding to present year and previous years and the revenue needs to be calculated only for current years data.

I tried modifying the expression as below but its not working.

= SUM({<Country= {"=Rank(SUM({<Year=2016,Dept=A>}Revenue),4)<=10"}>}Revenue)

Can you please have a look.

With regards,

Ayandeep

Re: How to display Top 10 rows in pivot table?

devarasu07 — Tue, 02 May 2017 12:23:14 GMT

Hi,

Try like below,

=SUM({<Country= {"=Rank(SUM({<Year={'2016'},Dept={'A'}>}Revenue),4)<=10"}>}Revenue)

feature report automation, create variable to pick latest year just create year variable use it

like,

vYear=Year(Max(Date))

vPrevYear=Year(Max(Date))

then use this variable in u r expression,

Prev. Year Revenue

=SUM({<Country= {"=Rank(SUM({<Year={$(vPrevYear)},Dept={'A'}>}Revenue),4)<=10"}>}Revenue)

Curr. Year Revenue

=SUM({<Country= {"=Rank(SUM({<Year={$(vYear)},Dept={'A'}>}Revenue),4)<=10"}>}Revenue)

Hope this helps you

Regards,

Deva

Re: How to display Top 10 rows in pivot table?

Tue, 02 May 2017 13:22:20 GMT

Hello,

Actually I want to show top 10 Company. I modified the code as below below.

=Sum({<[Company]={"=Rank(SUM({<[Current Year Number]={$(=Max([Current Year Number]))},[Month Number]={$(=Max([Month Number]))},[Dept]={'A'}>}[Operating Income]),4)<=10"}>}[Operating Income])

With this I am getting the Top 10 companies but the filters mentioned aren't applied and I am getting a summation of entire data for the Company.

Can you please help me out.

With regards,

Ayandeep

Re: How to display Top 10 rows in pivot table?

devarasu07 — Mon, 08 May 2017 13:04:02 GMT

Hi,

That filter selection field you need to add in group by clause (in that rank set analysis). can you share you app/mock document. thanks

Regards,
Deva