Top 3 products using AGGR

[Sean_BI]

Dear Team -

I need to display top 3 products in a straight table based on attached data.

when i select a specific product from the list box the straight table should display top3.

when i select two product from the list box, the straight table should display top3 of each product it total 6 rows i should get i.e first top 3 of product 1 and next top 3 of product 2.

Appreciate your help. I am struggling using AGGR and rank.

Rgds,

Sen.

[Kushal_Chawda]

Your Data only contains two products. How would you show top 3. Also for same Product and Same customer ID there is different Sold that means it's not a complete data.

Can you please provide more sample data with expected output?

[Sean_BI]

thanks Kush. 

I've updated the sheet. 

Sheet 1 has actual data and sheet2 is expected which displays Top 3.

Rgds,

Sean.

[tresesco]

Are you looking for something like:

[Sean_BI]

Also, i need to show only Two fields like

Product,Top3

rgds

Sean.

[tresesco]

Like this?

If so, go to presentation tab and use 'Hide Column' for customer code.

[Brett_Bleess]

Sean, have a look at the following Design Blog posts, they may give you another way to go on this one:

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis/ba-p/1468497

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684

Regards,
Brett

[Sean_BI]

The expected output should be as per attached. 

Refer to sheet "Expected 2" where each product is summed up based on top 2.

Example: Sum of Top2 price for Sky board is 98000

Similarly, Sum of Top2 price for Travel Equipment is 1100000 etc...

Appreciate if anyone can help on this ranking and using aggr().

[Kushal_Chawda]

Create a table chart

Dimension:

Product

Expression:

=sum(total <Product>aggr(if(rank( sum([Price($)]),0,4)<=2,sum([Price($)])),Product,[Customer Code]))

[Sean_BI]

Thank you Kush. This works for one month data. How to get this work for YTD. For example, I have data for April, May,  June and display top 20 YTD. 

Thank you, 

Sean.