topic Re: Calculate contribution margin in QlikView

Calculate contribution margin

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculate contribution margin

sunny_talwar — Wed, 21 Sep 2016 16:17:09 GMT

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

Re: Calculate contribution margin

Anonymous — Thu, 22 Sep 2016 05:57:56 GMT

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

Re: Calculate contribution margin

Anonymous — Thu, 22 Sep 2016 08:40:17 GMT

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.

Re: Calculate contribution margin

sunny_talwar — Thu, 22 Sep 2016 16:10:40 GMT

May be like this:

sales

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

cost of material

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

Re: Calculate contribution margin

Anonymous — Mon, 26 Sep 2016 06:01:04 GMT

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

Re: Calculate contribution margin

sunny_talwar — Mon, 26 Sep 2016 16:57:17 GMT

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?

Re: Calculate contribution margin

Anonymous — Mon, 26 Sep 2016 20:25:13 GMT

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

Re: Calculate contribution margin

sunny_talwar — Tue, 27 Sep 2016 17:18:57 GMT

That's what might be causing this

Re: Calculate contribution margin

Peter_Cammaert — Tue, 27 Sep 2016 19:05:47 GMT

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.