topic Count Sum in QlikView

Count Sum

lfholland — Wed, 15 Aug 2018 13:46:41 GMT

Is it possible to count the sum of one metric compared to the sum of another?

Currently, this is my expression:

Count(DISTINCT {<%Order={"=QtyOrd=QtyShip"},ShipMethod={'SO'},OrderDisposition-={'W'}>} OrderNumber)

It isn't giving me the right result. I was thinking if I could compare the sum of QtyOrd to the sum of QtyShip, it might work correctly...

Thanks in advance.

Re: Count Sum

sunny_talwar — Wed, 15 Aug 2018 13:54:56 GMT

You mean this?

Count(DISTINCT {<%Order={"=Sum(QtyOrd)=Sum(QtyShip)"}, ShipMethod={'SO'}, OrderDisposition-={'W'}>} OrderNumber)

Re: Count Sum

petter — Wed, 15 Aug 2018 13:56:12 GMT

A key is never a good field to use for any calculation purpose. The % as the first character of %Order indicate to me that this is a key.

I think that

Count(DISTINCT {<OrderNumber={"=QtyOrd=QtyShip"},ShipMethod={'SO'},OrderDisposition={'W'}>} OrderNumber)

might work for you...

But it depends on the fact that OrderNumber must be a unique row identificator in relation to the two fields QtyOrd and QtyShip. If not it will return a wrong count.

Re: Count Sum

lfholland — Wed, 15 Aug 2018 14:02:57 GMT

%Order is a key but it is also the unique identifier. Should I load another field that is %Order with a different name?

Re: Count Sum

petter — Wed, 15 Aug 2018 14:44:46 GMT

As long as it is a key it will appear in another table and then it will not be something that acts as a normal row identifier since a key field straddles two or more tables and might very well have a lot of extra values from the other tables....

So yes you will have to have some non-key row identifier to do the calculation in the search correctly if you are not using aggregates like Sunny showed you.