Contif based on sum aggrgated over another field

[#1]

Hi,

I would like to get monthly count of customers that have sum(amount)>300. There is something wrong with my syntax, but I can't figure out what...

=COUNT(DISTINCT {<Customer={"=SUM(Amount)>300"}>} Customer)

[Gysbert_Wassenaar · Accepted solution]

The syntax is fine, but you can't use set analysis to calculate this. Try sum(aggr(if(sum(Amount)>300,1,0),Month,Customer))

[#3]

Thanks! My own solution was write separate condition to each month, so that is considerably more elegant.

if(Month='1',count(DISTINCT{<Customer={"=sum({<Month={'1'}>}COMMISSION)>300"}>}Customer),

if(Month='2',count(DISTINCT{<Customer={"=sum({<Month={'2'}>}COMMISSION)>300"}>}Customer),

if(Month='3',count(DISTINCT{<Customer={"=sum({<Month={'3'}>}COMMISSION)>300"}>}Customer),

....