4 Replies Latest reply: Jun 27, 2010 12:46 PM by dacquet RSS

Using Count with IF expression

hngo2000

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
    John Witherspoon

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