Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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
Highlighted
MVP
MVP

Re: Help with count and if expression

Kindly provide sample data file !