topic Re: IF Statement for positive and negative values in QlikView

IF Statement for positive and negative values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm new to Qlik and have hit an issue when calculating my Gross Margin for a product, which hopefully will be an easy answer

I have an item which I have credited for £39, meaning my sales revenue is £-39, my Gross Profit on this item is therefore -£14.

I am then calculating my GM as (Sum ([Gross Profit]) / (Sum ([Sales Revenue]), however this produces a positive value of 36.8%, which should actually be a negative. I've structured the data going into the .qvd as a negative on GP if the SR is negative.

What I'm actually trying to achieve is this..

If the sum of Gross Profit/Sales Revenue is a negative, then produce a negative GM, else If the sym of Gross Profit/Sales Revenue is a positive then produce a positive GM.

Regards

David

Re: IF Statement for positive and negative values

Anonymous — Mon, 20 Jun 2016 11:10:59 GMT

Sorry, just to confirm - My data is being loaded from a .qvd as a negative - I want to add this calculation as a variable.

Re: IF Statement for positive and negative values

Clever_Anjos — Mon, 20 Jun 2016 12:15:13 GMT

Try using Sign(Sum ([Gross Profit])) * your expression

Re: IF Statement for positive and negative values

Anonymous — Mon, 20 Jun 2016 12:18:25 GMT

Hi David,

Use Fabs on denominator:

Sum([Gross Profit])/Fabs(Sum([Sales Revenue]))

As your Gross Profit is negative, your result will be negative.

Regards!!

Re: IF Statement for positive and negative values

effinty2112 — Mon, 20 Jun 2016 12:22:15 GMT

Hi David,

This is an arithmetic query rather than QV. The GM calculation of +36.8% is correct, I wouldn't change it. Bear in mind that it is a percentage of a negative amount -£39 so the calculation +36.8% of -£39 gives the correct profit of approx -£14.

If you massage the % to show it as a -ve value this would be misleading.

Whether you make a sales or a credit refund you need to keep the profit calculation consistent or else when you aggregate over a number of transactions you will get false results.

Cheers

Andrew

Re: IF Statement for positive and negative values

Anonymous — Mon, 20 Jun 2016 12:25:56 GMT

Hi,

Thanks for the replies, this is the current setup for the calculation, which produces the margin.

As suggested by Andrew, I need to be selective - So, if Total Sales <0 then Total Gross Profit, else If Total Sales =>0 then TGP/TS.

Re: IF Statement for positive and negative values

santiago_respane — Mon, 20 Jun 2016 12:28:27 GMT

Hi David,

i don't understand your issue, if you divide two negative numbers it will always return a positive result.

(Sum ([Gross Profit])= -14

(Sum ([Sales Revenue])= -39

(Sum ([Gross Profit]) / (Sum ([Sales Revenue])= 0.358 = 35.8%

Please elaborate more or share a sample app in order to help you with your issue.

Kind regards,

Re: IF Statement for positive and negative values

Anonymous — Mon, 20 Jun 2016 12:51:28 GMT

Hi Santiago,

What i'm attempting to do is check if both the numbers are a negative, and if so produce a negative value, if they aren't, then produce a positive value.

Re: IF Statement for positive and negative values

santiago_respane — Mon, 20 Jun 2016 13:00:13 GMT

Hi David,

you can try something like this:

=IF(Column(1)<0 and Column(2)<0,(Column(1)/Column(2))*-1,(Column(1)/Column(2)))

Please let me know if this helps.

Kind regards,

Re: IF Statement for positive and negative values

Anonymous — Mon, 20 Jun 2016 13:12:05 GMT

Yes, this is what I was after! Thank you.