topic Re: aggregate counts from different if conditions in QlikView

aggregate counts from different if conditions

Fri, 14 Sep 2012 17:36:57 GMT

Hello,
i have a complex set of conditions depending on values of the Fact table, each of which might result in a different figure (e.g. 3, 4 and 5 for each of the following).
=if(source1='1',(count( {$< STATUS = {'A', 'B'}>} DISTINCT ITEM_ID)),
//Next line for counting the items with Status = C and any Resolution except F or G.
 if(source='1',(count( {$< STATUS = {'C'},RESOLUTION= {"*"} -{'F', 'G'}>} DISTINCT ITEM_ID)),
  if(source='2',(count( {$< STATUS = {'A', 'D'}>} DISTINCT NS_BUG_ID)))))
I'm looking for the aggregate of those three (or two, or one) resulting figures, or zero if none of the conditions are met.
Having sum(aggr(  in front of everything above doesn't seem to work. I guess i'm using something wrongly...
Any ideas are welcome.
 
 Thanks!

Re: aggregate counts from different if conditions

whiteline — Fri, 14 Sep 2012 18:34:30 GMT

I'm looking for the aggregate of those three

What is your dimension to aggregate ?

Re: aggregate counts from different if conditions

Fri, 14 Sep 2012 19:02:31 GMT

Thanks for your quick hand!

I'm just counting distinct items.

I can make an example app.if that helps...

Re: aggregate counts from different if conditions

Fri, 14 Sep 2012 19:11:42 GMT

Btw, i noticed a small typo in my example above (source1 instead of source), but that's not what's breaking my code

Re: aggregate counts from different if conditions

Fri, 14 Sep 2012 19:35:10 GMT

Found another typo (NS_bug istead of ITEM) that i fixed too, but doesn't solve it. Don't know how to attach the sample_A.qvw file i prepared...

Re: aggregate counts from different if conditions

Fri, 14 Sep 2012 19:38:00 GMT

aah... got to use the advanced editor to attach files 🙂

Re: aggregate counts from different if conditions

whiteline — Fri, 14 Sep 2012 19:59:34 GMT

Ok, try to enclose your expression with something like this:

=Sum(aggr(...put your complex expression here..., status))

But I see the problem with your if expression:

=if(status='1', ...this works when status=1...,

if(status='1', ...this never works...,

if(status='2', ...this works when status=2)))

Try to understand how the if works and how you use it.

Re: aggregate counts from different if conditions

Fri, 14 Sep 2012 21:26:54 GMT

Thanks! You're right; using IF statements overcomplicates a simple sum that can also be done like this (now it works!)
  
=(count( {$< STATUS = {'A', 'B'}, source= {'1'}>} DISTINCT ITEM_ID))+
//Next line for counting the items with Status = C and any Resolution except F or G.
(count({$< STATUS = {'C'}, source= {'1'},RESOLUTION= {"*"} -{'F', 'G'}>} DISTINCT ITEM_ID))+
(count({$< STATUS = {'A', 'D'}, source= {'2'}>} DISTINCT ITEM_ID))