Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with count and if expression


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.

1 Reply
MK_QSL
MVP
MVP

Kindly provide sample data file !