Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
Sorry, just to confirm - My data is being loaded from a .qvd as a negative - I want to add this calculation as a variable.
Try using Sign(Sum ([Gross Profit])) * your expression
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!!
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
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.
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,
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.
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,
Yes, this is what I was after! Thank you.