count and set analysis

[zekazak]

Hello,

I'm struggling with one set analysis expression. I've got a chart object like this:

AccounID	AccountName	Expression1	Expression2 (Y or N)
1111	AAAA	10	N
2222	BBBB	20	Y
3333	CCCC	30	Y

where

Expression1:

=count({$<CASHJOURNALTYPE.paymenttype={1},CashCalDate={">=$(=date(now()-1,'YYYY-MM-DD'))"}>}CASHJOURNAL.linkcashjournalid)

Expression2:

=if(count({$<CASHJOURNALTYPE.paymenttype={1},CashCalDate={">=$(=date(now()-1,'YYYY-MM-DD'))"}>}CASHJOURNAL.linkcashjournalid)>=20,'Y','N')

Both expression works fine. But I want to get a count of AccountID's where Expression2 = 'Y' in separate text object. In my example expected result is 2. How the set analysis expression should look like?

Any idea how to get it done would be highly appriciated. Thanks in advance!

~Sergejs

[swuehl · Accepted solution]

I would try with something like this:

=sum( aggr(

if(count({$<CASHJOURNALTYPE.paymenttype={1},CashCalDate={">=$(=date(now()-1,'YYYY-MM-DD'))"}>}CASHJOURNAL.linkcashjournalid)>=20,1,0),

AccountID))

I think you don't need to change your set expression, but aggregate the results of the expression grouped by AccountID.

Hope this helps,

Stefan

[zekazak]

Hi Stefan,

Yep, Thanks. It works. You've saved my life.

Best Regards,

~Sergejs