topic Re: Need Help with function If in QlikView

Need Help with function If

Thu, 28 Jan 2016 07:08:36 GMT

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

Re: Need Help with function If

Anonymous — Thu, 28 Jan 2016 07:51:53 GMT

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)

Re: Need Help with function If

asgardd2 — Thu, 28 Jan 2016 07:53:44 GMT

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)

)

Re: Need Help with function If

Peter_Cammaert — Thu, 28 Jan 2016 08:03:38 GMT

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

Re: Need Help with function If

Thu, 28 Jan 2016 08:06:59 GMT

Thanks a lot it's work perfectly

Best regards