Aggr() in each row

[oboute]

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],

          0)

     )

, [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!

[sunny_talwar · Accepted solution]

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],

          0)

    )

, [Concat N° Fournisseur++])

or this

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

          if(

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

          [Montant en devise comptable],

          0)

    )

[oboute]

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],

          0)

    )

)

[qlikcook]

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