# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Contributor

## calculating gross margin

Hi,

I want to calculate a gross margin. I am using 2 tables:

table1

 GLACCOUNT GLACCOUNTNAME type3 10000 sales prod 1 revenu 10001 sales prod 2 revenu 10003 sales prod 3 revenu 20001 COS prod 1 COS 20002 COS prod 2 COS 20003 COS prod 3 COS 30001 wages wages 30002 car lease other 30003 fuel other 30010 lease building other 30011 stationary other 30012 insurances other

table2

 GLACCOUNT GLACCOUNTNAME AMOUNT 10000 sales prod 1 -1000 10001 sales prod 2 -1500 10003 sales prod 3 -750 20001 COS prod 1 500 20002 COS prod 2 700 20003 COS prod 3 100 30001 wages 50 30002 car lease 25 30003 fuel 10 30010 lease building 10 30011 stationary 5 30012 insurances 9

how can i calculate the gross margin by using type3 in table1 (revenu - COS in money and in %)?

Tags (1)
3 Replies
New Contributor III

## calculating gross margin

if you are sure your GLACCOUNTNAME field is always constructed in the same way, you can try the following:

in the script

Revenu:

select substr(GLACCOUNTNAME, 7, 6) as product, amount as revenue

from table1 inner join table2 on table1.glaccount = table2.glaccount

where type3 = 'revenue'

Cost:

select substr(GLACCOUNTNAME, 5, 6) as product, amount as cost

from table1 inner join table2 on table1.glaccount = table2.glaccount

where type3 = 'COS'

Qlikview will create two tables 'revenu' and 'Cost' and link them togheter on product.

This will allow you to calculate the gross margin by product.

Using a description to determine the product is not very safe, if you have a table wit the link between the GLACCOUNT and the product code, it would be better to use that

New Contributor III

## Re: calculating gross margin

if you are sure your GLACCOUNTNAME field is always constructed in the same way, you can try the following:

in the script

Revenu:

select substr(GLACCOUNTNAME, 7, 6) as product, amount as revenue

from table1 inner join table2 on table1.glaccount = table2.glaccount

where type3 = 'revenue'

Cost:

select substr(GLACCOUNTNAME, 5, 6) as product, amount as cost

from table1 inner join table2 on table1.glaccount = table2.glaccount

where type3 = 'COS'

Qlikview will create two tables 'revenu' and 'Cost' and link them togheter on product.

This will allow you to calculate the gross margin by product.

Using a description to determine the product is not very safe, if you have a table wit the link between the GLACCOUNT and the product code, it would be better to use that

Contributor

## calculating gross margin

Hi Greta,

Thnx for your reply, but actually I want to do something far more simple (I think). The only thing I want to do, is sum(revenu)-sum(COS) and (sum(revenu)-sum(COS))/sum((revenu).

knd rgds

Hans