topic Re: How to calculate sales margin with columns from 2 tables in QlikView

How to calculate sales margin with columns from 2 tables

IvanaGru90 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to calculate sales margin with columns from 2 tables

alex00321 — Mon, 06 Jan 2020 07:45:44 GMT

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

Re: How to calculate sales margin with columns from 2 tables

Brett_Bleess — Mon, 06 Jan 2020 14:24:10 GMT

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

Re: How to calculate sales margin with columns from 2 tables

IvanaGru90 — Thu, 09 Jan 2020 12:12:32 GMT

Hi,

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

Re: How to calculate sales margin with columns from 2 tables

alex00321 — Mon, 20 Jan 2020 07:06:15 GMT

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