Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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