How to sum distinct?

[Anonymous]

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?

[sunny_talwar · Accepted solution]

May be this:

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

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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

[sunny_talwar]

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

Also, You need a comma before AutoID

[neelamsaroha157]

Try this

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

[Anonymous]

You were right, the name of the field was different

[Anonymous]

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

    6, Carl, 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.