topic count and set analysis in QlikView

count and set analysis

zekazak — Thu, 10 Nov 2011 13:57:14 GMT

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

count and set analysis

swuehl — Thu, 10 Nov 2011 15:18:12 GMT

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

count and set analysis

zekazak — Thu, 10 Nov 2011 15:21:48 GMT

Hi Stefan,

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

Best Regards,

~Sergejs