topic Using Count with IF expression in QlikView

Using Count with IF expression

Tue, 02 Mar 2010 19:06:32 GMT

Why is it that the expression below return the right value (the ProjectID if true and Null if false):

if (Sum(ProjectTime) = 0, ProjectID)

But this expression return everything Null:

Count (Distinct if (Sum(ProjectTime) = 0, ProjectID))

Using Count with IF expression

johnw — Tue, 02 Mar 2010 19:32:33 GMT

I'm guessing that when you say the first expression works, you mean that it works in a chart with a dimension of ProjectID? And when you say the second expression returns everything null, you mean in a text box or something like that?

If so, the first expression works because the chart forces it to only look at one ProjectID at a time. And the second expression doesn't work because it looks at all ProjectIDs at the same time, not one at a time. So it sums the ProjectTime across ALL ProjectIDs, which probably isn't 0, so it returns null.

If I'm guessing what you're after correctly, you need to tell QlikView to evaluate your if() expression for one ProjectID at a time instead of all at once, just like you do in the chart I'm guessing you have. You do that with an aggr(...,ProjectID). Like this, I think:

count(distinct aggr(if(sum(ProjectTime)=0,ProjectID),ProjectID))

Using Count with IF expression

Tue, 02 Mar 2010 19:57:54 GMT

Thank you so much. It works perfectly.

Using Count with IF expression

Fri, 25 Jun 2010 18:00:41 GMT

Hi guys,

I have a problem similar but I can't not solve it with your solution.

My dimension is a category of product (for which I put a target)

Count(distinct if ( InvoicedQty>Target and YYYYMM <= '200901' and YYYYMMlis >200801 , Customer))

but I want sum of InvoiceQTY

I tried:

Count(distinct aggr( if SUM(InvoicedQty) >Target and YYYYMM <= '200901' and YYYYMMlis >200801 , Customer),CATEGORY)

but it returns nothing.

Can you help me ?

Using Count with IF expression

Sun, 27 Jun 2010 16:46:40 GMT

Found it :

sum(if( aggr( sum(if (YYYYMM <= '200901' and YYYYMM >200801,InvoicedQty)),CATEGORY,Customer)>=Target,1,0))