calculating gross margin

[qlikhans]

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

thanks for your help.

[Anonymous]

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

[Anonymous]

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

[qlikhans]

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