Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Aggr() in each row

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.

1 Solution

Accepted Solutions
MVP

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)

)

3 Replies
MVP

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)

)

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)

)

)

Contributor III

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

Community Browser