topic Re: Display top 20 materials in QlikView

Display top 20 materials

Wed, 22 Jun 2016 08:52:09 GMT

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

Re: Display top 20 materials

Anonymous — Wed, 22 Jun 2016 09:24:00 GMT

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.

Re: Display top 20 materials

Wed, 22 Jun 2016 10:47:02 GMT

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

Re: Display top 20 materials

Anil_Babu_Samineni — Wed, 22 Jun 2016 10:54:36 GMT

James,

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

- Anil

Re: Display top 20 materials

muthukumar77 — Wed, 22 Jun 2016 10:59:35 GMT

Hi,

Try in dimension,

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

Re: Display top 20 materials

Anonymous — Wed, 22 Jun 2016 11:16:22 GMT

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')

Re: Display top 20 materials

Wed, 22 Jun 2016 11:55:17 GMT

Muthu,

Your expression displays empty pie.

Re: Display top 20 materials

muthukumar77 — Thu, 23 Jun 2016 04:33:42 GMT

Hi,

Can you send me a Qvw file?

Re: Display top 20 materials

Thu, 23 Jun 2016 07:23:47 GMT

Muthu,

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

-James

Re: Display top 20 materials

muthukumar77 — Thu, 23 Jun 2016 10:05:48 GMT

Hi,

try like this,

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

Re: Display top 20 materials

Thu, 23 Jun 2016 10:15:11 GMT

Muthu,

Your latest expression also dint work Attached one is the expected result in pie chart.

NOTE:

User should be able to see TOP 20 sales share of the total sales.

Re: Display top 20 materials

qliksus — Fri, 24 Jun 2016 10:34:18 GMT

Try something like this

Dimension = Valuelist( 'Top20','Total')

Expression: if( Valuelist( 'Top20','Total')='Top20' , sum({$<[Fiscal Year]= {$(=Year(Today()))},Material={"=

rank(sum({$<[Fiscal Year]= {$(=Year(Today()))}>} ([In company code currency POS]/RateFixed))),Material))<21"}>} ([In company code currency POS]/RateFixed))),Material) ,

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