topic Calculate Margin with variable and fixed costs in QlikView

Calculate Margin with variable and fixed costs

Wed, 25 Nov 2020 16:16:04 GMT

Hi.

I am brand new to Qlikview.

I've collected my data with an MySQL Database and it works fine.

I've also started to implement the first dashboard in Qlik Cloud. But now i am struggling with the absolute margin of sales.

I have 4 tables:

Branches	Employees	Orders	Products
id	id	id	id
energy_costs	salary	branch-id	selling_price
rental_costs	branch-id	buying-time	buying_price
		product-id
		quantity
		buying-time

The calculation of the margin works like charm with this formula (devision through 100 cuz the values saved as cent):

((Sum(price) - Sum(buying_price)) * Count(quantity)) / 100

How i am able to calculate the absolute margin including the energy costs, rental costs and salary of every employee linked to its branch? The costs and salary counting only once per month.

I was trying with this formula but doesn't work - the margin is higher than the total income:

(((Sum(price) - Sum(buying_price)) * Count(quantity)) - (Sum(energy_costs) + Sum(rental_costs) - Sum(salary)) / 100

Thanks for your help.

Best C

Re: Calculate Margin with variable and fixed costs

sunny_talwar — Fri, 11 Aug 2017 15:25:08 GMT

Would you be able to share some sample data or a sample application to show the issue?

Re: Calculate Margin with variable and fixed costs

Sat, 12 Aug 2017 15:35:58 GMT

Hi Sunny,

thanks for attention 🙂

Here is a link to the whole app, i think this help us most - Dropbox - FOM BI.qvf

Thank you a lot, Sunny!

Best C

Re: Calculate Margin with variable and fixed costs

sunny_talwar — Mon, 14 Aug 2017 11:44:04 GMT

Unfortunately, I won't be able to download from DropBox... would you be able to attach here:

Uploading a Sample

Re: Calculate Margin with variable and fixed costs

Mon, 14 Aug 2017 16:42:16 GMT

Hi Sunny,

i didn't see the button first. Thank you for your hint.

The app is now available via the Attachment.

Thank you.

Best C