how to get the top 2 and bottom 2 values sum and deduct that from total sum in the specified period

Anonymous — Thu, 13 Oct 2016 11:40:09 GMT

I want to find out the top 2 values and bottom 2 values for each Product and Region for the dates between (8/11/2016 and 8/21/2016) and deduct that (top 2 + bottom 2 sum ) from the total sum.

Prodcut	Region	Date	Sales
Prodcut1	US	8/11/2016	100
Prodcut1	US	8/12/2016	1200
Prodcut1	US	8/13/2016	600
Prodcut1	US	8/14/2016	700
Prodcut1	US	8/15/2016	300
Prodcut1	US	8/16/2016	1500
Prodcut1	US	8/17/2016	450
Prodcut1	US	8/18/2016	1600
Prodcut1	US	8/19/2016	1000
Prodcut1	US	8/20/2016	1800
Prodcut1	US	8/21/2016	200
Prodduct1	US	8/22/2016	600

Product	Region	Sales
Product1	US	5750

Total Sum -> sum({<Date={'>=8/11/2016<=8/21/2016'}>}Sales)

Top 2 Sales Expression -> sum({<Date ={"=aggr(rank(sum({<Date ={'>=8/11/2016<=8/21/2016'}>}Sales))<=2,Product,Region,Date )"}>} Sales)

Bottom 2 Sales Expression -> sum({<Date ={"=aggr(rank(-sum({<Date ={'>=8/11/2016<=8/21/2016'}>}Sales))<=2,Product,Region,Date )"}>} Sales).

I got the solution for this problem, Please refer the attached application

Re: how to get the top 2 and bottom 2 values sum and deduct that from total sum in the specified period

sunny_talwar — Thu, 13 Oct 2016 18:05:58 GMT

Is this resolved now? Just want to make sure?

Re: how to get the top 2 and bottom 2 values sum and deduct that from total sum in the specified period

Anonymous — Thu, 13 Oct 2016 19:26:08 GMT

it was resolved..Thanks Sunny for checking

topic how to get the top 2 and bottom 2 values sum and deduct that from total sum in the specified period in QlikView

how to get the top 2 and bottom 2 values sum and deduct that from total sum in the specified period

Re: how to get the top 2 and bottom 2 values sum and deduct that from total sum in the specified period

Re: how to get the top 2 and bottom 2 values sum and deduct that from total sum in the specified period