Simple Count

[#1]

I'm new so please be gentle

I’m struggling with a simple count in QV. I’d like to count the distinct projects which have no Revenue for any year. In this case the result should be 2 (1 for ID 1 and 1 for ID 3). Can you help? I’ve attached my mockup.

[Josh_Good]

Hi,

Either of these expression will work. I recommend using the second one because it is more efficient.

=Sum(Aggr(if(Sum(Revenue) > 0, 1),ProjectID))

=Count(DISTINCT {$<Revenue = {">0"}>} ProjectID)

Please mark the appropriate replies as helpful / correct so our team and other members know that your question(s) has been answered to your satisfaction.

-Josh

PS I've also move this thread to the QlikView area so it is easier for others to find.

[demoustier]

in attached file

[#4 · Accepted solution]

Thanks Josh, these are returning a count of 1. The correct result should be a count of 2

This one works though

Sum(Aggr(if(Sum(Revenue) = 0, 1),ProjectID))

[#5]

Thanks Demoustier. Can this be done in an expression? I need it to work with dynamic selections

[Josh_Good]

Sorry I miss read your question.  I thought you were looking for projects with revenue not without revenue.  The Aggr function will be the better choice then as you already figured out.

=Sum(Aggr(if(Sum(Revenue) = 0, 1),ProjectID))