Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
sai_katare
Contributor III
Contributor III

Help with Set Analysis expression

Hi All, 

I am stuck with a requirement. Following are the details:

I have columns: 1. Customer    2. Quantity      3. Sales      4. Dates

I need to create a text box for 

(i) Customer with minimum sales for quantity >0 and between last 24 months.

(ii) Customer with maximum sales for quantity >0 and between last 24 months.

Something like aggr({<quantity>0}{<today()-24months>}Customer)

Please help..

Labels (1)
1 Reply
Vegar
MVP
MVP

 

Sample data:

LOAD * inline [
Customer,Quantity,Sales,Dates
Skinix,213,2832.72,2018-09-09
Babbleopia,297,920.98,2018-09-15
Skibox,680,4176.98,2018-07-09
Fatz,776,493.60,2019-02-23
Meembee,58,5253.99,2018-06-02
Dablist,731,926.57,2018-08-22
Linkbuzz,-900,-7147.04,2019-02-28
Ooba,822,1714.85,2018-01-27
Zoomlounge,839,2021.48,2018-11-18
Oloo,938,3241.51,2018-01-07
Demimbu,783,6733.75,2018-01-24
Brightdog,514,3070.53,2018-01-07
]; 

Minimum company

 

 

FirstSortedValue(Customer,aggr(sum({<Quantity={">0"},Dates={">=$(=addmonths(today(),-12))"}>}Sales),Customer) )

Maximum cpmpany

FirstSortedValue(Customer,-aggr(sum({<Quantity={">0"},Dates={">=$(=addmonths(today(),-12))"}>}Sales),Customer) )