3 Replies Latest reply: Aug 23, 2011 10:30 AM by Hans kleijnen

# 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 %)?

• ###### 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

• ###### 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

• ###### 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