Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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
DateSales
Prodcut1US8/11/2016100
Prodcut1US8/12/20161200
Prodcut1US8/13/2016600
Prodcut1US8/14/2016700
Prodcut1US8/15/2016300
Prodcut1US8/16/20161500
Prodcut1US8/17/2016450
Prodcut1US8/18/20161600
Prodcut1US8/19/20161000
Prodcut1US8/20/20161800
Prodcut1US8/21/2016200
Prodduct1US8/22/2016600

  

ProductRegionSales
Product1US5750

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

it was resolved..Thanks Sunny for checking

View solution in original post

2 Replies
sunny_talwar

Is this resolved now? Just want to make sure?

Anonymous
Not applicable
Author

it was resolved..Thanks Sunny for checking