Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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

May be like this:

sales

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

cost of material

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

View solution in original post

9 Replies
Highlighted

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

Highlighted
Contributor III
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:


example.png   

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

May be like this:

sales

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

cost of material

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

View solution in original post

Highlighted
Contributor III
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.

Thanks again for your help

best regards

Lukas

Highlighted

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

That's what might be causing this

Highlighted

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.

Afterwards, you can load your facts as:

LOAD [%cost type],

     applymap('other table', [%cost type], Null()) AS [cost type],

     measure,

     :

FROM ...;

which simplifies your data model. On condition that every account only belongs to a single cost type.