How to display Top 10 rows in pivot table?

[#1]

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

[devarasu07]

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

[#3]

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

[devarasu07]

Hi,

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

FYI,

[#5]

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

[devarasu07 · Accepted solution]

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

[#7]

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

[devarasu07]

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