Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Kindly provide sample data file !