Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

gjamila_esq
Contributor

Aggr function

Hi all,

I have this situation:

2012_12_17_magazzini.JPG

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
gjamila_esq
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.CdTMagazzini.JPG

The correct total is 1650, not 1200.

alfasierra
Contributor II

Re: Aggr function

Can u post new documente with Num.Doc 527?

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

CatturaCdT_2012_12_20.JPG

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

Re: Aggr function

Try this

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

hope it helps

gjamila_esq
Contributor

Re: Aggr function

Works!! Thank you so much!

Community Browser