Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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) )