# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted Contributor III

## Calculate contribution margin

Hey Qlik Community,

I'm very new to qlikview so I don't know whether my request is simple or not.

In my fact table I got a column named "account". Like account 810222. In a dimension table I have defined which account belongs to which cost type like account 810222 is "sales" and account 561991 is "cost of material".

Now I want to calculate the contribution margin of each order. But the only expression I can create is sum(account).

Is it possible to manipulate the fact table, so I'm able to calculate

sum(sales)

sum(cost of sales) etc.?

Thank you in advance for any kind of help!

Lukas 1 Solution

Accepted Solutions
Highlighted MVP

May be like this:

sales

Sum({<[cost type] = {'sales'}>}measure)

cost of material

Sum({<[cost type] = {'cost of material'}>}measure)

9 Replies
Highlighted MVP

Where are you wanting to do this? Front end, back end? What type of chart you have? and dimensions?

Highlighted Contributor III

Hi Sunny,

thanks for your response. I think the front end solution would be better. The fact table will be loaded from sap directly after the basic structure is done.

If it is possible I would prefer a simple table with the cost types in the rows and for example different customers as columns:

sales

./. construction costs

./. material costs

...

=contribution margin

As I've said, the fact table contains different accounts which are defined in another table

acc. 12345 to 12499 --> sales

acc. 23456 to 23499 --> constuction costs

Only thing I can calculate is sum(account) with the type of costs as a dimension: So if it is possible, I want to calculate sum(construction costs) etc. Turn it into a pivot or s.th. else and calculate the contribution margin.

Again, thank you in advance for your help and sorry for my bad english Highlighted Contributor III

Maybe this wording is better I got the dimension "cost type" in the fact table. in this column there are accounts listet.

%cost type          measure

1234                    1.000 €

1235                    2.000€

1236                    1.000€

2345                    -700€

2346                    -500€

In another table, I have defined which account belongs to which cost type

%cost type     cost type

1234               sales

1235               sales

1236               sales

2345               cost of material

2346               cost of material

I want to calculate sum(sales) and not sum(1234)+sum(1235)+sum(1236) etc.

Highlighted MVP

May be like this:

sales

Sum({<[cost type] = {'sales'}>}measure)

cost of material

Sum({<[cost type] = {'cost of material'}>}measure)

Highlighted Contributor III

Hey Sunny,

I tried your expression, but it didn't work

but:

sum(if([cost type]='sales', [measure]))

works just fine!

Don't know if i had a typo in your expression but it helped me with the if expression.

best regards

Lukas

Highlighted MVP

I feel that it should have worked. Don't see a reason why it won't. Is cost type and measure coming from different tables?

Highlighted Contributor III

Yes, the cost types are defined in another table than the measures.

fact table:

%cost type          measure

12345               15€

12456               10€

other table:

%cost type       cost type

12345               sales

12456               cost of material

Highlighted MVP

That's what might be causing this Highlighted MVP

Sunny's suggestion using set analysis should work just fine.

To simplify your situation and omit one hop, you could embed the %cost type->cost type mapping table in your facts table as an additional dimension field. Load the "other table" as a mapping table with %cost type as index and cost type as mapping value.  