topic Margin calculation with missing values in QlikView

Margin calculation with missing values

Sat, 31 Jul 2010 10:17:22 GMT

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

Margin calculation with missing values

Sat, 31 Jul 2010 16:25:22 GMT

Can you post the sample QV file

Nimish

Margin calculation with missing values

Sat, 31 Jul 2010 18:13:55 GMT

Maybe you can try

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

Nimish

Margin calculation with missing values

Sat, 31 Jul 2010 22:52:10 GMT

Thank You Numish

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

Margin calculation with missing values

Mon, 02 Aug 2010 01:19:26 GMT

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

Margin calculation with missing values

Mon, 02 Aug 2010 08:27:02 GMT

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

Margin calculation with missing values

Mon, 02 Aug 2010 15:49:05 GMT

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

Margin calculation with missing values

Tue, 03 Aug 2010 16:50:56 GMT

You got it!

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

Thanks again!

Paolo