topic Re: sum problem in QlikView

sum problem

egoziyan — Sun, 19 May 2013 18:44:38 GMT

G'day,

I have a problem that I don't understand.

I have a table that show the quantity that have to be collected (QTY) and the the actual qty that was collected (QTY_ACTUAL) the products are divided to families and I want to callulate onlt family 10 that was actually collected.

The data is

FAMILY, QTY, ACTUAL_QTY

10,100,80

20,50,50

10,30,0

10,50,20

the result should be 100 / 150 = 66%

I'm using the following statement

SUM({<FAMILY={'10'}>}QTY_ACTUAL)/if(SUM({<FAMILY={'10'}>}QTY_ACTUAL) = 0,0,SUM({<FAMILY={'10'}>}QTY))

My problem is that it calculate it as 10 / 180 and give me 55.55%

any help will be appreciated

Yaniv

Re: sum problem

Gysbert_Wassenaar — Sun, 19 May 2013 18:59:26 GMT

The set is calculated per chart, not per row or record. The if statement is not having any effect on that. So sum({<FAMILY={10}>}QTY) will return 180 and not 150. Try this instead:

sum({<FAMILY={10}>}ACTUAL_QTY)/sum({<FAMILY={10},ACTUAL_QTY-={0}>}QTY)

Re: sum problem

egoziyan — Mon, 20 May 2013 14:49:17 GMT

Thanks you Gysbert

Can you explain to me the

/sum({<FAMILY={10},ACTUAL_QTY-={0}>}QTY)

part as it gives me the wrong answer.

In my example I'm getting 80 + 20 = 100 which is the actual qty rather then 100+50 = 150

it also give me red underline from the -= sign (like an error in the syntax)

Thanks

Yaniv

Re: sum problem

swuehl — Mon, 20 May 2013 15:20:27 GMT

I do get the correct answer 150 with Gysbert's solution. Have you double checked that your expression is identical?

The red underline is an error in the syntax checker (it doesn't recognize the -= operator properly), which you can ignore in this case. The status of the expression dialog should say 'Syntax ok'.

You can also use this, should be equivalent to above:

sum({<FAMILY={10}, ACTUAL_QTY = {"<>0"}>} QTY)