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
