How to calculate sales margin with columns from 2 tables

[IvanaGru90]

Hello everyone,

Can you help me with expression for calculating a sales margin? I have two tables:

Table 1:

Order ID, Line NO, Product ID, Quantity, Order Unit Price, Discount

Table 2:

Product ID, Product Name, Category ID, Quantity Per Unit, Unit Cost, Unit Price, Units In Stock, Units On Order

I thought that sales margin should calculate like:

(Sum (Quantity*(Order Unit Price*(1-Discount)))-Sum (Quantity*Unit Price))/Sum (Quantity*(Order Unit Price*(1-Discount)))

But I am not sure about this field Quantity, or it is not correct at all?

Thank you for your answers

[alex00321]

Hi how about you attach a sample file and clarify your expected result? Thanks!

[Brett_Bleess]

Ivana, I think it might be better to just have Google searched this one.  Here is a pretty decent link I found that should help confirm your thoughts I hope:

https://www.accountingtools.com/articles/how-to-calculate-sales-margin.html

Hopefully that will help you translate things in your use case to get the correct result.  Feel free to Google yourself for other examples too, I got a ton of hits, and I searched for "sales margin definition".  Shout if I have misunderstood what you needed.

Example QVW files can be helpful by the way, as it does let folks see your data model and your sheet objects etc. to be able to see everything directly to try to be sure you are doing the correct things, but in this case, I am not sure it is needed if I have hit on the right thing for you.  If you were more trying to figure out the correct QlikView syntax, then a sample QVW will likely be very helpful.  

Regards,
Brett

[IvanaGru90]

Hi,

I attached a sample file. My results for sales margin are mostly negative, but I am not sure that it is correct.

[alex00321]

Hi Based on what your formula dictated, your result seems correct. Most are negative but it also returns positive value.