Aggr function

[Anonymous]

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!

[Gysbert_Wassenaar]

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.

[Anonymous]

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.

[Anonymous]

Can u post new documente with Num.Doc 527?

[Anonymous]

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".

[Gysbert_Wassenaar · Accepted solution]

Maybe like this then:

if(Dimensionality()=11,

   sum(distinct VEN_QTA_ORD),

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

[CELAMBARASAN]

Try this

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

hope it helps

[Anonymous]

Works!! Thank you so much!