Calculate contribution margin

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[Anonymous]

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.

[sunny_talwar · Accepted solution]

May be like this:

sales

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

cost of material

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

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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

[sunny_talwar]

That's what might be causing this

[Peter_Cammaert]

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.