Display top 20 materials

[#1]

Hi,

I have a table in my report (see attached 't20_mat_existing_table') which displays all the records. I want it to show only top 20 materials (rows) which has highest 2016(YTD) , change%. Result should look like the table attached 't20_mat_proposed_table_pie'.

NOTE:

2016(YTD)  = sum({$<[Fiscal Year]= {$(=Year(Today()))}>} ([In company code currency POS]/RateFixed))

change % = (sum({$<[Fiscal Year]= {$(=Year(Today()))}>} ([In company code currency]/RateFixed))-sum({$<[Fiscal Year]= {$(=Year(Today())-1)}>} ([In company code currency]/RateFixed)))/sum({$<[Fiscal Year]= {$(=Year(Today())-1)}>} ([In company code currency]/RateFixed))

-James

[Anonymous]

Hi,

For your table, you can sort by expression in the sort tab.

For your chart, you can use the dimension limits.

See attachments with screenshots as an example.

[#3]

Thanks Filip,

Your example worked for me in table. Great !!

Now i need the pie chart to display the  sum of Top 20 sales in 2016(YTD)  against sum of all the materials' 2016(YTD). How to achieve this ?

TIA,

James

[Anil_Babu_Samineni]

James,

filip_duchateau Continuation, If you want to do in Set Analysis, Then we require your Application.

- Anil

[muthukumar77]

Hi,

Try in dimension,

AGGR(IF(Rank(sum({$<[Fiscal Year]= {$(=Year(Today()))}>} ([In company code currency POS]/RateFixed)), 20)<=20, Material), Material, 'Others')

[Anonymous]

Hi James,

That's great.

For your chart, please try Muthukumar's suggestion :

Try in dimension,

AGGR(IF(Rank(sum({$<[Fiscal Year]= {$(=Year(Today()))}>} ([In company code currency POS]/RateFixed)), 20)<=20, Material), Material, 'Others')

[#7]

Muthu,

Your expression displays empty pie.

[muthukumar77]

Hi,

Can you send me a Qvw file?

[#9]

Muthu,

sorry , the size is huge.. i have attached the script..

-James

[muthukumar77]

Hi,

try like this,

=if(aggr(sum({$<[Fiscal Year]= {$(=Year(Today()))}>} ([In company code currency POS]/RateFixed))),Material)<=20,Material,'Others')