Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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))
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))
Thank you so much. It works perfectly.
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 ?
Found it :
sum(if( aggr( sum(if (YYYYMM <= '200901' and YYYYMM >200801,InvoicedQty)),CATEGORY,Customer)>=Target,1,0))