Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

aggregate counts from different if conditions

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!

7 Replies
whiteline
Honored Contributor II

Re: aggregate counts from different if conditions

I'm looking for the aggregate of those three

What is your dimension to aggregate ?

Not applicable

Re: aggregate counts from different if conditions

Thanks for your quick hand!

I'm just counting distinct items.

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

Not applicable

Re: aggregate counts from different if conditions

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

Not applicable

Re: aggregate counts from different if conditions

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

Not applicable

Re: aggregate counts from different if conditions

aah... got to use the advanced editor to attach files :-)

whiteline
Honored Contributor II

Re: aggregate counts from different if conditions

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.

Not applicable

Re: aggregate counts from different if conditions

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

Community Browser