topic Re: Set Analysis - Group By in Where Clause in App Development

Set Analysis - Group By in Where Clause

kcville — Mon, 20 Dec 2021 21:38:40 GMT

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

Re: Set Analysis - Group By in Where Clause

jonathandienst — Tue, 04 Feb 2020 06:12:27 GMT

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)

Re: Set Analysis - Group By in Where Clause

Kashyap_R — Tue, 04 Feb 2020 06:14:58 GMT

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

Re: Set Analysis - Group By in Where Clause

kcville — Thu, 06 Feb 2020 01:21:35 GMT

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