topic Re: Count distinct Problem in QlikView

Count distinct Problem

marcel_olmo — Mon, 28 Oct 2013 14:41:27 GMT

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 :

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

Here I attach my test in Qlikview.

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

Regards, Marcel.

swuehl — Mon, 28 Oct 2013 14:49:11 GMT

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 — Mon, 28 Oct 2013 14:53:00 GMT

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 — Mon, 28 Oct 2013 14:57:44 GMT

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

Regards, Marcel.

israrkhan — Mon, 28 Oct 2013 15:04:56 GMT

Hi,

Find the attachment, its working...