Set Analysis - Group By in Where Clause

[kcville]

Hello

I am trying to write Set Analysis to do the following:

Count  distinct customers from sales transactions for a particular date range that have total sales <> 0

SQL would look something like this..

Select distinct customer from table where (select customer, Sum(sales) from table where payment date >=01/01/2018 and <=31/12/2018 Group by customer);

Thanks in Advance

[jonathandienst]

This may work:

Count({<customer = {"$(payment_date >= '01/01/2018' and payment_date <='31/12/2018'"}>} distinct customer)

Or this

Count({<customer = P({<payment_date ={>='01/01/2018' <='31/12/2018'}>})>} distinct customer)

 (depends on your actual data model)

[Kashyap_R]

Hi

try this 

Count(Distinct {<payment date={">=01/01/2009 and <=01/09/2009"}>}CustomerID) 

if this is showing 0 values and Counting them then try this

Count(Distinct {<payment date={">=01/01/2009 and <=01/09/2009"},0={">$(=Aggr(Sum(sales),CustomerID))"}>}CustomerID)

Hope this helps

Thanks

[kcville]

Thank you for your replies, however these are not working.

I really need to select the date cohort first and then check for aggregate sum greater than 0.

something like this..

If(payment_date >='01/01/2018' and <'31/12/2019', Count({$<customer={"=aggr(Sum(payment),customer) > 0"}>}distinct customer)

Thank you