SQL If condition in Qlik Sense

[Vaku1606]

Hello,

Just developing my first Qlik report and got stuck with one condition.

I have one field called FKART which represents billing type. What I want to do is to get negative sales (sales filed is FKIMG) whenever billing type is equal to S1 or ZS2 (invoice cancellation). Otherwise, sales should be normal: sum(FKIMG).

Could you please help?

Thanks in advance and regards

[rubenmarin]

Hi, if you want to sum invoice cancellation only when sales are negative you'll also need a field that define the invoice, like InvoiceNumber or InvoiceID. One option to sum negative cancellatons and non-cancelation invoices could be:

- Sum({<FKART={'S1','ZS2'},InvoiceID={"Sum(FKIMG)<0"}>+<FKART-={'S1','ZS2'}>} FKIMG)

OR: Sum(Aggr(If((Match(FKART,'S1','ZS2') and Sum(FKIMG)<0) OR not Match(FKART,'S1','ZS2'), Sum(FKIMG),InvoiceID))

[Vaku1606]

Hello,

thanks for the feedback.

Actually, all numbers coming from SAP are positive (FKIMG has always positive values), but I want to convert them to negative based on billing type selection. This would mean: if FKART = S1 or ZS2 then FKIMG should be FKIMG * (-1). The field which represents Invoice ID is VBELN.

If you could please help.

Thanks once again

[rubenmarin · Accepted solution]

Hi, to do that it will be better if you have the amount calculated in script:

If(Match(FKART,'S1','ZS2'), FKIMG*-1, FKIMG) as InvoiceAmount

In expression there are different options:

- Sum(FKIMG*If(Match(FKART,'S1','ZS2'),-1,1))

- Sum({<FKART-={'S1','ZS2'}>} FKIMG)-Sum({<FKART={'S1','ZS2'}>} FKIMG)