IF Statement for positive and negative values

[Anonymous]

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

[Anonymous]

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

[Clever_Anjos]

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

[Anonymous]

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!!

[effinty2112]

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

[Anonymous]

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.

[santiago_respane]

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,

[Anonymous]

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.

[santiago_respane · Accepted solution]

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,

[Anonymous]

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