topic Re: Simple Count in QlikView

Simple Count

Mon, 22 Sep 2014 16:06:48 GMT

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.

Re: Simple Count

Josh_Good — Mon, 22 Sep 2014 16:25:17 GMT

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.

Re: Simple Count

demoustier — Mon, 22 Sep 2014 17:08:32 GMT

in attached file

Re: Simple Count

Mon, 22 Sep 2014 17:55:43 GMT

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))

Re: Simple Count

Mon, 22 Sep 2014 17:56:22 GMT

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

Re: Simple Count

Josh_Good — Mon, 22 Sep 2014 18:04:11 GMT

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))