Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qlikhans
Contributor

calculating gross margin

Hi,

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

table1

GLACCOUNTGLACCOUNTNAMEtype3
10000sales prod 1revenu
10001sales prod 2revenu
10003sales prod 3revenu
20001COS prod 1COS
20002COS prod 2COS
20003COS prod 3COS
30001wageswages
30002car leaseother
30003fuelother
30010lease buildingother
30011stationaryother
30012insurancesother

table2

GLACCOUNTGLACCOUNTNAMEAMOUNT
10000sales prod 1-1000
10001sales prod 2-1500
10003sales prod 3-750
20001COS prod 1500
20002COS prod 2700
20003COS prod 3100
30001wages50
30002car lease25
30003fuel10
30010lease building10
30011stationary5
30012insurances9

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

thanks for your help.

Tags (1)
3 Replies
vandrieg
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

vandrieg
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

qlikhans
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

Community Browser