Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using Count with IF expression

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





1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

4 Replies
johnw
Champion III
Champion III

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

Not applicable
Author

Thank you so much. It works perfectly.

Not applicable
Author

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 ?

Not applicable
Author

Found it :

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