Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Margin calculation with missing values

I'm looking for a way for calculating a global gross margin when some cost values are missing. I mean, I'm using this expression for margin calculation:

sum(Price-Cost) / sum(Price)

The problem is, I have some item where Cost is missing. Therefore, I would like to discard these rows in the calculation in order to have a realistic total margin (in other words: global margin of the items with a cost). Of course, I could simply filter off the rows with missing cost from my table, but I would like to avoid that.
I tried with

if(Cost>0, sum(Price-Cost) / sum(Price) )

This is OK row by row, but the total row ends up as a null value. Then I tried

sum({$<Cost={'>0'}>} Price-Cost) / sum({$<Cost={'>0'}>} Price)

This is correct row by row as well, but the total value has nothing to do with the "correct" one.
Any suggestion?

Thanks

1 Solution

Accepted Solutions
Not applicable
Author

Paolo,

Went through your excel file. What I noticed is that for the global marin (my margin column), your total Sales column includes the sale price of the item which has zero cost. As a result, you are getting the margin of 37.66. If you remove this sale value (103.26) from the total, you will get a margin of 36.98. This is exactly what you will get in QV using the set analysis which you have mentioned in your post.

sum({$<Cost={'>0'}>} Price-Cost) / sum({$<Cost={'>0'}>} Price)

For convinience, I have taken your sample excel data in qv and recreated the situation.

Do let me know if I have understood you problem correctly.

Nimish

View solution in original post

7 Replies
Not applicable
Author

Can you post the sample QV file

Nimish

Not applicable
Author

Maybe you can try

Sum(if(Cost>0,Price-Cost,0))/Sum(if(Cost>0,Price,0))

Nimish

Not applicable
Author

Thank You Numish

here it is a sample. Sorry for the italian names of the fields.

Not applicable
Author

Sorry Paolo but cannot understand the sample you have uploaded. The dimensions which are shown are different than the ones used in the expression so am having a difficult time to understand what you are tyring to do. Can you put it in excel and tell what is it that you are expecting

Nimish

Not applicable
Author

Sorry, I had to simplify an huge document in order to be attached and some dimensions were obtained in different way.
Basically, as you can see in the xls file, I would like my total margin to be (Total sales-Total cost)/(Total sales), but I get different values. I know there's something I'm missing, there should be a way....

Thanks again

Not applicable
Author

Paolo,

Went through your excel file. What I noticed is that for the global marin (my margin column), your total Sales column includes the sale price of the item which has zero cost. As a result, you are getting the margin of 37.66. If you remove this sale value (103.26) from the total, you will get a margin of 36.98. This is exactly what you will get in QV using the set analysis which you have mentioned in your post.

sum({$<Cost={'>0'}>} Price-Cost) / sum({$<Cost={'>0'}>} Price)

For convinience, I have taken your sample excel data in qv and recreated the situation.

Do let me know if I have understood you problem correctly.

Nimish

Not applicable
Author

You got it!

What I was missing is simply the quotes in the ">0" string. Too bad...

Thanks again!

Paolo