topic How to sum distinct? in QlikView

How to sum distinct?

Anonymous — Wed, 21 Dec 2016 14:19:16 GMT

Hi,

I am trying to perform an expression that will sum the count field when the ID is distinct.

How can I do this?

I have tried SUM ({$<DISTINCT ID>} [Case Count])

But it's not working.

Any idea?

Re: How to sum distinct?

sunny_talwar — Wed, 21 Dec 2016 14:37:16 GMT

May be this:

Sum(Aggr(Only([Case Count]), ID))

Re: How to sum distinct?

Anonymous — Wed, 21 Dec 2016 14:41:37 GMT

It's not working, something is not correct in that expression.

Re: How to sum distinct?

sunny_talwar — Wed, 21 Dec 2016 14:46:43 GMT

Are you get incorrect output or error? Would you be able to show few rows of data and explain what you are looking to get as an output?

Re: How to sum distinct?

Anonymous — Wed, 21 Dec 2016 14:50:46 GMT

This is what I am getting, it's not recognizing the AutoID as field.

Re: How to sum distinct?

sunny_talwar — Wed, 21 Dec 2016 14:55:49 GMT

It seems to be not recognizing Case Count. Isn't Case Count a field?

Also, You need a comma before AutoID

Re: How to sum distinct?

neelamsaroha157 — Wed, 21 Dec 2016 14:59:39 GMT

Try this

Count({$<ID={"=Sum(Aggr(Count(ID),ID))=1"}>}[Case Count])

Re: How to sum distinct?

Anonymous — Wed, 21 Dec 2016 15:02:15 GMT

You were right, the name of the field was different

Re: How to sum distinct?

Anonymous — Wed, 21 Dec 2016 15:11:00 GMT

you can make it in the script using the peek()

I put an example that can help you, I prefer use that in the script because the performance is better if you use that in the application.

Ejemplo:

LOAD * INLINE [

ID, Alumno, CaseCount

1, En, 1

2, An, 2

3, Ali, 1

4, Dui, 1

5, Hal, 1

6, Carl, 1

7, Adri, 1

2, An, 2

1, En, 1

3, Ali, 1

8, Ema, 1

];

NoConcatenate

Ejemplo_Ordenado:

Load ID,

Alumno,

CaseCount,

if( ID=peek(ID,-1),0,CaseCount) as CaseCountSum

Resident Ejemplo

order by ID;

drop table Ejemplo;

You can see what I put in order the field 'ID', because the function peek () depend about that.

So, in the application you can put the next;

=sum(CaseCountSum)

Note that field that we use is de new field that we aggregate using the funtion peek().

Regards.