Need to get Top 10 Deals by Revenue

[harvinder901]

Hi Experts,

I have a sample data which represents Sales of various products for n deals in several months from Jan to July. (See sample sheet attached for reference).

My requirement is that if user selects any month from Jan to July, then top 10 records sorted by Sales value (Desc to Asc) upto the selected month should be displayed to user. For example : If I select Jan, then top 10 records of Jan should be filtered or If I select March, then top 10 records from Jan to March should be filtered.

I have tried using AGGR function with Rank for this but, the issue that I am facing is if any of the filtered result has any record with duplicate deal name in the whole sheet then that record is also getting filtered in the result.

For ex : I have a deal with name Xerox 198 having sales 228.41 in Jan (part of top 10 for Jan) and same deal name having sales 196.85 (not in top 10). My result of top 10 should include deal with Sales value of 228.41 but in actual row of Sales value 196.85 is also getting included in result set due to duplicate deal name value.

Please help me how to get the correct data. Any help would be much appreciated. Thanks in advance.

[Taoufiq_Zarra]

Hi,

Does this solution help to answer your request?

on load add the line RecNo() as Id,

then pivot table :

dimension 1: Date

dimension 2 : Add calculated dimension and use the script:

=aggr(if( rank(sum(Sales),4)<=10,Deals), Id)

on expression use olso this expression : sum(Sales)

you can get a result like this

you can olso add ID as a dimension to get

Cheers,

[harvinder901]

Thanks Taoufiq,

One thing I forgot to mention is I have columns coming from different tables .For the sake of ease, I have shared in one excel. But In my data model Deals are coming from one table, Product from other and Date from another.

[Taoufiq_Zarra]

Can you test see what happens if you put the ID in the Deals table?

[Brett_Bleess]

Here are a couple of Design Blog posts that I think are what you are needing:

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

Here is the base URL to the Design Blog area for future reference as well, so you can search yourself:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett