Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
)
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)
)
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)
)
)
Thanks Sunny!
I had another situation but the NODISTINCT function was what I needed.