# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Contributor

## Aggr function

Hi all,

I have this situation:

For the same article I have 1 document associated with the customer and for the same document associated with the customer I have 2 or more warehouse documents.

Therefore I have many rows associated with the same customer. And this is correct.

My problem is in the total. As you see in the picture, the field "Ordinato" is a quantity. Sum(Ordinato) is wrong because in this way I have total in all rows.

Sum(distinct Ordinato) returns the values I expect but is not logically correct.

sum(aggr(sum(Ordinato),fornitore,numdoc,linea,cod.articolo,cod.colore,dt doc stireria,nr doc stireria)) doesn't work.

Also

sum(aggr((Ordinato),fornitore,numdoc,linea,cod.articolo,cod.colore,dt doc stireria,nr doc stireria)) has column fields correct but it calculates wrong total.

I would like to have 860.00 as total and

605.00,

605.00,

605.00,

605.00,

255.00

in columns

How can I do this?

Thank you!

1 Solution

Accepted Solutions

## Re: Aggr function

Maybe like this then:

if(Dimensionality()=11,

sum(distinct VEN_QTA_ORD),

sum(aggr(sum(distinct VEN_QTA_ORD), C_COLORE)))

talk is cheap, supply exceeds demand
7 Replies

## Re: Aggr function

 Sum(distinct Ordinato) returns the values I expect but is not logically correct.

Actually, it is logically correct in this case. It calculates exactly what you're asking for. The sum of the distinct values of Ordinato over all the pivot dimensions you wish to consider in calculating the sum. You already found your solution.

talk is cheap, supply exceeds demand
Contributor

## Re: Aggr function

Thank you but this is not the right solution because I can have different "cod.colore" with the same "ordinato" qty. In this way, doing sum(distinct ordinato) I'll lose a lot of qty.

The correct total is 1650, not 1200.

Contributor II

## Re: Aggr function

Can u post new documente with Num.Doc 527?

Contributor

## Re: Aggr function

I'm not able to attach qvw file in this post, so I attaced it in first post (QLIK_MAGAZZINI_R.1.1_test.qvw).

Actually the only solution I've found is to concat "dt doc stireria" and "nr doc stireria".

## Re: Aggr function

Maybe like this then:

if(Dimensionality()=11,

sum(distinct VEN_QTA_ORD),

sum(aggr(sum(distinct VEN_QTA_ORD), C_COLORE)))

talk is cheap, supply exceeds demand
MVP

## Re: Aggr function

Try this

if(RowNo() <> 0, sum( VEN_QTA_ORD), Sum(DISTINCT VEN_QTA_ORD))

hope it helps

Contributor

## Re: Aggr function

Works!! Thank you so much!