topic Re: Aggr() in each row in QlikView

Aggr() in each row

oboute — Tue, 05 Sep 2017 14:45:17 GMT

Hello,

I want to do like SUMIF in excel :

I want the same result in QV, so in 1 the total sum for each same name in column A.

Here, there is 3 lines with name "1740FSCLOYER 2017", sum is 145 200€ (290 400 - 72 300 - 72 600). Others, sum gives 0.

In my qv, i tried to do an aggregate :

Aggr(

Sum(

if(

[Date d'écriture]>=$(vDatedWritingStart) and [Date d'écriture]<=$(vDatedWritingEnd),

[Montant en devise comptable],

)

, [Concat N° Fournisseur++])

I take the amount (if my dates conditions are ok), and i aggregate them by my field "concat N° fournisseur" but it put the sum (145 200€) only on first line he find :

The column "montant" next to it is just the sum with dates conditions.

I can't switch to pivot table because i need an exportable table to excel with this style.

Thanks in advance!

Re: Aggr() in each row

sunny_talwar — Tue, 05 Sep 2017 16:57:57 GMT

Not sure I understand, but what if you do this

Aggr(NODISTINCT

Sum(

if(

[Date d'écriture]>=$(vDatedWritingStart) and [Date d'écriture]<=$(vDatedWritingEnd),

[Montant en devise comptable],

)

, [Concat N° Fournisseur++])

or this

Sum(TOTAL <[Concat N° Fournisseur++]>

if(

[Date d'écriture]>=$(vDatedWritingStart) and [Date d'écriture]<=$(vDatedWritingEnd),

[Montant en devise comptable],

)

Re: Aggr() in each row

oboute — Wed, 06 Sep 2017 07:33:09 GMT

Thank you so much !!

I didn't know we could do a total specifying the dimension, that was exactly what i searched for.

So i took your method, adding the condition to avoid show it on lines where dates are not ok, so finally :

if([Date d'écriture]>=$(vDatedWritingStart) and [Date d'écriture]<=$(vDatedWritingEnd),

Sum(TOTAL <[Concat N° Fournisseur++]>

if(

[Date d'écriture]>=$(vDatedWritingStart) and [Date d'écriture]<=$(vDatedWritingEnd),

[Montant en devise comptable],

)

Re: Aggr() in each row

qlikcook — Tue, 28 Feb 2023 10:35:11 GMT

Thanks Sunny!
I had another situation but the NODISTINCT function was what I needed.