Count distinct Problem

[marcel_olmo]

Hi guys, I have a table of movements that describes a state of a code.

This code could be returned, and when is returned, it can be returned as "return", "qreturn" or "freturn".

I'm trying to count how many codes of each type of returned code I have in my document.

But I don't know why it counts in a weird way.

This is what I get :

Mov	Mov Detail	Counter
lending	freturn	1
	qreturn	203
	return	320
	Total	411
Total		411

And this is my desired result (if I count properly) :

Mov	Mov Detail	Counter
lending	freturn	1
	qreturn	203
	return	320
	Total	524
Total		524

Here I attach my test in Qlikview.

Do you know how is this happening? What I'm doing wrong?

Regards, Marcel.

[swuehl · Accepted solution]

That's because your Code you count may relate to several Mov Detail values for the same Mov value.

Try

=sum(aggr( count(distinct {$< Returned ={'1'} >} Code ) , Mov, [Mov Detail]))

to do a sum-of-rows total in your pivot table.

[bbi_mba_76]

Hi,

maybe I don't understand.

For example for code 9788401339080 you have both qreturn and return as [Mov Detail].

So this code is counted once in the total, and it is right.

[marcel_olmo]

Thanks guys! With Aggr() I did my purpose!

Regards, Marcel.

[israrkhan]

Hi,

Find the attachment, its working...