Need Help with function If

[#1]

Hello,

I have a problem to calculate a function.

I have two document types, an invoice and a credit note.

When I have invoice the amount is positive, but when I have a credit note I will wish him to return a negative amount.

I tried this formula but it does me no return value:

If( (Type_Facture) = 'Avoir', sum({ <Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}>} -MontantHT_Ligne) or  If( (Type_Facture) = 'Facture', sum({ <Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}>} MontantHT_Ligne)

Anyone can help me?

Best regards

[Anonymous]

Hi,

Looks like there is an issue with your syntax

Please try this and see if it works:

If( (Type_Facture = 'Avoir', sum({ <Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}>} -MontantHT_Ligne), If( Type_Facture = 'Facture', sum({ <Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}>} MontantHT_Ligne)

[asgardd2]

You need change you expression with correct syntax.

If( Type_Facture = 'Avoir',

sum({ <Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}>} -MontantHT_Ligne),

  If( Type_Facture = 'Facture',

   sum({ <Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}>} MontantHT_Ligne)

     )

  )

[Peter_Cammaert]

Something like this?

sum({<Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}, Type_Facture = {'Facture'}>} MontantHT_Ligne)

- sum({<Date_Facture={'>=$(vDateDebCalendrier)<=$(vDateFinCalendrier)'}, Type_Facture = {'Avoir'}>} MontantHT_Ligne)

Because Invoice types can change per row, the IF() should be placed inside your sum() aggregation function. And because an IF() that is executed once per row is incredibly bad for performance when processing large amounts of invoices, we use set analysis again to separate Invoices from Credit Notes.

Bonne chance,

Peter

[#5]

Thanks a lot it's work perfectly

Best regards