How to show Customers with Sales per Year using set analysis

[DaithiOK]

Hi all,

I'm trying to return a list of Customers that have spent over $10k each year in total while spending less than $2k on a particular category in each of those years. 

I feel that I am close but just missing one small aspect. Just learning set analysis and these questions seem to be stumping me. 

If i build it up piece by piece i have the following:

Sum( {  <CompanyName = {"=Sum(Sales)>10000"}> }Sales)

--This returns customers that have spent over $10k in total. 

Sum( { <CompanyName = {"=Sum({<[Category Name] = {'Seafood'}>}Sales)<2000"} > * <CompanyName = {"=Sum(Sales)>10000"}> }Sales)

--This intersects all those customers that have spent over $10k in total with those that have spent less than $2k on Seafood in total and would return companies like the following:

We can see that total spend was >10k and total seafood spend was <2k. 

I just can't seem to figure out how to show those customers that have spent these Totals PER YEAR. As in the figures above for Seafood could be 1999,1500,900 where the distinction is that in each year the spend was less than 2k but in total it may have been more. 

In order to help me understand better can i ask that the question be answered in 2 parts based on my 2 set analyses above. 

1. First how would i change my set analysis to show customers that spent more than 10k per year.
2. And then how would i change the second to show the intersection.

The Year field is just called 'Year'.

Sorry if i overexplained the problem. Thanks so much for your help. 

[sunny_talwar · Accepted solution]

I don't think set analysis will work (unless you add a new field in the script which combines Company Name and Year field into a single field. But that will not necessarily give you a better performance than using Aggr() function. You can try this

1)

Sum(Aggr(If(Sum(Sales) > 10000, Sum(Sales)), CompanyName, YearField))

2)  

Sum(Aggr(If(Sum(Sales) > 10000 and Sum({<[Category Name] = {'Seafood'}>} Sales) < 2000, Sum(Sales)), CompanyName, YearField))