Help with count and if expression

Tue, 10 Jun 2014 02:13:50 GMT

Hi,

I have Accounts and Tasks. I want to count the number of accounts where no tasks have been performed in the account (in a text box). I have 3 status types for Tasks (1, -1 and 0). What I mean by "where no tasks have been performed" is where tasks is either a status of 0 or it can have no entries at all. I also want to only count those accounts that are expiring this quarter.

So my thought was to count the number of task type 1 and -1, and if they add to zero count it.

My expression in set analysis is as follows - which works:

= Sum(Aggr(if (count ({<Delivered -= {'0'}>} distinct Account) = 0, count (Account), 0), Account))

However, if I want to narrow it down by adding:

= Sum(Aggr(if (count ({<Delivered = {'0'} distinct Account) = 0, EndQuarterYear = {'Q' & ceil(month(today())/3) & '-' & Year (Today())}>}, count (Account), 0), Account))

It gives me a 0 result. Is the issue set analysis? How do I write this as an 'IF' statement? I tried the following:

= count (if (EndQuarterYear = 'Q' & ceil(month(today())/3) & '-' & Year (Today())and count (Delivered <> 0) = 0, Account))

= sum(aggr(if (count (Delivered <> 0) = 0 and EndQuarterYear = 'Q' & ceil(month(today())/3) & '-' & Year (Today()), count (Account), 0),Account))

Thanks for any help.

Re: Help with count and if expression

MK_QSL — Tue, 10 Jun 2014 08:46:34 GMT

Kindly provide sample data file !

topic Help with count and if expression in QlikView

Help with count and if expression

Re: Help with count and if expression