Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
oboute
Contributor III
Contributor III

Aggr() in each row

Hello,

I want to do like SUMIF in excel :

2017-09-05_13h51_43.png

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 :

2017-09-05_16h38_21.png

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!

1 Solution

Accepted Solutions
sunny_talwar

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)

    )

View solution in original post

3 Replies
sunny_talwar

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

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