topic Re: Top 3 products using AGGR in QlikView

Top 3 products using AGGR

Sean_BI — Sat, 16 Nov 2024 01:36:40 GMT

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.

Re: Top 3 products using AGGR

Kushal_Chawda — Wed, 01 Jan 2020 17:56:39 GMT

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?

Re: Top 3 products using AGGR

Sean_BI — Thu, 02 Jan 2020 06:04:25 GMT

thanks Kush.

I've updated the sheet.

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

Rgds,

Sean.

Re: Top 3 products using AGGR

tresB — Thu, 02 Jan 2020 06:25:37 GMT

Are you looking for something like:

Re: Top 3 products using AGGR

Sean_BI — Thu, 02 Jan 2020 07:16:06 GMT

Also, i need to show only Two fields like

Product,Top3

rgds

Sean.

Re: Top 3 products using AGGR

tresB — Thu, 02 Jan 2020 07:35:55 GMT

Like this?

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

Re: Top 3 products using AGGR

Brett_Bleess — Thu, 02 Jan 2020 15:03:51 GMT

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

Re: Top 3 products using AGGR

Sean_BI — Sat, 04 Jan 2020 15:52:08 GMT

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().

Re: Top 3 products using AGGR

Kushal_Chawda — Sat, 04 Jan 2020 19:50:52 GMT

Create a table chart

Dimension:

Product

Expression:

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

Re: Top 3 products using AGGR

Sean_BI — Sun, 05 Jan 2020 06:38:00 GMT

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.

Re: Top 3 products using AGGR

Kushal_Chawda — Sun, 05 Jan 2020 11:02:31 GMT

share sample data to work with?

Re: Top 3 products using AGGR

Sean_BI — Sun, 05 Jan 2020 12:19:00 GMT

Thank you for your response kush.

I've attached the sample data.

Rgds,

sean.

Re: Top 3 products using AGGR

Kushal_Chawda — Sun, 05 Jan 2020 13:24:41 GMT

try below

=sum(total <Product>{<Period,Year,Month,Period={">=$(=yearstart(max(Period)))<=$(=max(Period))"}>} aggr(if(rank( sum({<Period,Year,Month,Period={">=$(=yearstart(max(Period)))<=$(=max(Period))"}>}[Price($)]),0,4)<=2, sum({<Period,Year,Month,Period={">=$(=yearstart(max(Period)))<=$(=max(Period))"}>}[Price($)])),Product,[Customer Code]))