Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone
I am building a sales model and calculated GP by [Sales Value]-[Cost Value].
I now want to calculate the percentage but not using "Relative" as it calculates out of 100%
I want to see my records going over 100%. How can I do this?
The classical definition of Gross Profit Margin is "% of Profit compared to Sales"
(Sales - Cost) / Sales
This definition, however, will never go over 100%, but it's not the same as using "Relative" either.
The calculation suggested by Gysbert is used in some countries. It represents the "Markup %" - what's the % of your profit, compared to the initial cost:
(Sales - Cost) / Cost
This calculation can go over 100%, but I'd be careful calling it "Gross Profit Margin %", because you might be misleading your users.
I'm describing many of these business definitions, along with the corresponding QlikView techniques, in my new book QlikView Your Business - coming out soon. Check it out on Amazon!
best,
Oleg Troyansky
www.masterssummit.com - take your QlikView skills to the next level!
sum([Sales Value])/sum([Cost Value]) -1
The classical definition of Gross Profit Margin is "% of Profit compared to Sales"
(Sales - Cost) / Sales
This definition, however, will never go over 100%, but it's not the same as using "Relative" either.
The calculation suggested by Gysbert is used in some countries. It represents the "Markup %" - what's the % of your profit, compared to the initial cost:
(Sales - Cost) / Cost
This calculation can go over 100%, but I'd be careful calling it "Gross Profit Margin %", because you might be misleading your users.
I'm describing many of these business definitions, along with the corresponding QlikView techniques, in my new book QlikView Your Business - coming out soon. Check it out on Amazon!
best,
Oleg Troyansky
www.masterssummit.com - take your QlikView skills to the next level!
Thanks guy
Hi Oleg,
I tried to make an expression for the first definition in your answer above ( (Sales-Cost)/Sales), and it looked like this:
sum(Sales)-sum(productCost*Quantity)/sum(sales)
Obviously that one wouldn't work, not mathematically either.
Naturally I'd like to have the expression like this, but this won't give me any data:
(sum(Sales)-sum(productCost*Quantity))/sum(Sales)
Any thoughts on how I can solve this?
The answer below by gwassenaar works but I feel that it's not exactly what I'm after.
sum(Sales)/sum([productCost*Quantity) -1
Hi kristofferb,
I see no reasons for your formula not to work, except for a simple misspelling - for example, most of your formulas contain the field Sales, while one of them spells sales with all lower case letters.
If you'd like someone to troubleshoot your expressions, please post a small sample of the app and the formula that's not working for you.
cheers,
Oleg Troyansky
Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Munich, Germany!