topic Re: Gross Profit% in QlikView

Gross Profit%

Fri, 20 Mar 2015 13:45:22 GMT

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?

Re: Gross Profit%

Gysbert_Wassenaar — Fri, 20 Mar 2015 13:57:43 GMT

sum([Sales Value])/sum([Cost Value]) -1

Re: Gross Profit%

Oleg_Troyansky — Fri, 20 Mar 2015 14:07:00 GMT

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!

Re: Gross Profit%

Fri, 20 Mar 2015 18:41:07 GMT

Thanks guy

Re: Gross Profit%

Tue, 10 Jan 2017 04:10:54 GMT

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

Re: Gross Profit%

Oleg_Troyansky — Tue, 10 Jan 2017 18:52:48 GMT

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!