Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
dpmbrouwers
Contributor III
Contributor III

Another Count Aggr question

Dear Qlik sense friends and wizards,

I need some help with  the following challenge.

I have order-data in which goods movements are coded. I'm trying to count the orders in which the qty of goods with a certain movement type  (202) exceed the qty of another movement type (551).

So far, this is what I have been able to come up with:
Count(Aggr(If(Count({$<movement type={'202'}>}[qty])-Count({$<movement type={'551'},[qty]>}distinct[Order])>'0', 1,0),Order))

I'm almost there, but I cannot figure out why the result also shows the orders where the qty's are equal and the orders where 551 exceeds 202.  (In the image, the orders in rows 69 and 80 should be excluded, but they're not.)

Help is appreciated.

Labels (1)
1 Solution

Accepted Solutions
agigliotti
Partner - Champion
Partner - Champion

maybe this:
=Sum(
Aggr(
if( Count( {< movement type = {'202'} >} [qty] )
-
Count( {< movement type = {'551'} >} [qty] )
> 0, 1, 0 ), Order )
)

I hope it can helps.

View solution in original post

2 Replies
agigliotti
Partner - Champion
Partner - Champion

maybe this:
=Sum(
Aggr(
if( Count( {< movement type = {'202'} >} [qty] )
-
Count( {< movement type = {'551'} >} [qty] )
> 0, 1, 0 ), Order )
)

I hope it can helps.

dpmbrouwers
Contributor III
Contributor III
Author

Hi Agliotti,

This seems to do the trick.
I see you're using sum instead of count, but cannot figure out why.  Could you explain why my solution was wrong?