topic Re: Aggr function in QlikView

Aggr function

Anonymous — Mon, 17 Dec 2012 16:28:26 GMT

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,

255.00

in columns

How can I do this?

Thank you!

Re: Aggr function

Gysbert_Wassenaar — Mon, 17 Dec 2012 16:37:37 GMT

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.

Re: Aggr function

Anonymous — Thu, 20 Dec 2012 08:31:03 GMT

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.

Re: Aggr function

Anonymous — Thu, 20 Dec 2012 09:15:10 GMT

Can u post new documente with Num.Doc 527?

Re: Aggr function

Anonymous — Thu, 20 Dec 2012 10:18:21 GMT

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

Gysbert_Wassenaar — Thu, 20 Dec 2012 14:00:44 GMT

Maybe like this then:

if(Dimensionality()=11,

sum(distinct VEN_QTA_ORD),

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

Re: Aggr function

CELAMBARASAN — Thu, 20 Dec 2012 14:11:03 GMT

Try this

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

hope it helps

Re: Aggr function

Anonymous — Thu, 20 Dec 2012 14:19:30 GMT

Works!! Thank you so much!